【MySQL】MySQLで使えるJOIN全部試してみた

この記事は [MySQL Advent Calendar 2023](https://qiita.com/advent-calendar/2023/mysql) 20日目の記事です。

## はじめに

MySQLの[JOIN句のリファレンス](https://dev.mysql.com/doc/refman/8.0/ja/join.html)を読んでいたところ、JOINにさまざまな種類があることに気がついた。
普段の業務では`INNER JOIN`もしくは`LEFT OUTER JOIN`しかほぼ使用していないが、興味本意で他のJOINはどのような機能なのかを調べてみた。

## 動作確認環境

```
# mysql --version
mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
```

## ざっくりと結論

MySQLで使えるJOINは以下の6つ。

- INNER JOIN（内部結合）
- LEFT OUTER JOIN（左外部結合）
- RIGHT OUTER JOIN（右外部結合）
- CROSS JOIN（クロス結合）
- NATURAL JOIN（自然結合）
- STRAIGHT_JOIN

以降、それぞれのJOINについて紹介していく。

## INNER JOIN（内部結合）

### 機能
結合したテーブルの両方に存在する行だけを取得する。

### 書き方
```sql
from
  テーブル名
inner join テーブル名 on
  結合条件
```

### 例

以下のテーブルからコメントをユーザ名付きで取得したい。

- コメントテーブル(comments)

|id|user_id|contents|
|---|---|---|
|1|1|コメント1-1|
|2|1|コメント2-1|
|3|2|コメント3-2|
|4|3|コメント4-3|
|5|10|コメント5-10|

- ユーザテーブル(users)

|id|name|
|---|---|
|1|田中|
|2|佐藤|
|3|今井|
|4|岡田|
|5|長井|

- SQL
```sql
select
  c.user_id
  , u.name
  , c.contents
from
  comments c
inner join users u on
  c.user_id = u.id
```

- 結果

|user_id|name|contents|
|---|---|---|
|1|田中|コメント1-1|
|1|田中|コメント2-1|
|2|佐藤|コメント3-2|
|3|今井|コメント4-3|

## LEFT OUTER JOIN（左外部結合）

### 機能
結合条件に合致しない場合でも先に指定したテーブルの行はすべて取得する。
結合条件に合致しない場合、後に指定したテーブルの列はすべてNULLとなる。

### 書き方
```sql
from
  テーブル名
left [outer] join テーブル名 on
  結合条件
```
outerは省略可能。
### 例

以下のテーブルから、存在しないユーザのコメントを含めてコメントを取得したい。

- コメントテーブル(comments)

|id|user_id|contents|
|---|---|---|
|1|1|コメント1-1|
|2|1|コメント2-1|
|3|2|コメント3-2|
|4|3|コメント4-3|
|5|10|コメント5-10|

- ユーザテーブル(users)

|id|name|
|---|---|
|1|田中|
|2|佐藤|
|3|今井|
|4|岡田|
|5|長井|

- SQL
```sql
select  
  c.user_id  
  , u.name  
  , c.contents  
from  
  comments c  
left outer join users u on  
  c.user_id = u.id
```

- 結果

`user_id` が10のデータは先に指定したテーブル（comments）には存在するが、後に指定したテーブル（users）には存在しないため、nameの値がNULLになる。

|user_id|name|contents|
|---|---|---|
|1|田中|コメント1-1|
|1|田中|コメント2-1|
|2|佐藤|コメント3-2|
|3|今井|コメント4-3|
|10|NULL|コメント5-10|

## RIGHT OUTER JOIN（右外部結合）

### 機能
後に指定したテーブルの行はすべて取得し、  条件に一致しない場合は先に指定したテーブルの列がすべてNULLとなる。
つまり、LEFT OUTER JOINの逆。

### 書き方
```sql
from
  テーブル名
right [outer] join テーブル名 on
  結合条件
```

### 例

LEFT OUTER JOINの例をRIGHT OUTER JOINで書き直す。
from句に指定するテーブルとjoin句で指定するテーブルを反対にして、leftをrightに変更する。

- SQL
```sql
select  
  c.user_id  
  , u.name  
  , c.contents  
from  
  users u
right outer join comments c on 
  c.user_id = u.id
```

- 結果

LEFT OUTER JOINの時と同じ結果になる。

|user_id|name|contents|
|---|---|---|
|1|田中|コメント1-1|
|1|田中|コメント2-1|
|2|佐藤|コメント3-2|
|3|今井|コメント4-3|
|10|NULL|コメント5-10|


## CROSS JOIN（クロス結合）

### 機能
指定したテーブルのすべての組み合わせを取得する。
MySQLではINNER JOINと構文上等価であるが、標準SQLでは異なるため注意が必要。
INNER JOIN は ON句 とともに使われ、CROSS JOIN は ON句 を使わない。

### 書き方
```sql
from
  テーブル名
cross join
  テーブル名
```
### 例

以下のテーブルから、商品がとりうるすべての組み合わせを列挙したい。

- 商品テーブル(products)

|id|name|
|---|---|
|1|机|
|2|ボール|
|3|パソコン|

- 色テーブル(colors)

|id|name|
|---|---|
|1|赤|
|2|青|
|3|黄|

- SQL
```sql
select  
  p.id  
  , p.name  
  , c.id  
  , c.name  
from  
  products p  
cross join  
  colors c  
;
```

- 結果

すべての組み合わせなので、それぞれのテーブルの行数を掛け合わせた数だけ行が出力される。
今回であれば商品テーブルが3行、色テーブルが3行のため、 3 × 3 で 9行出力される。

| p.id | p.name | c.id | c.name |
| :--- | :--- | :--- | :--- |
| 3 | パソコン | 1 | 赤 |
| 2 | ボール | 1 | 赤 |
| 1 | 机 | 1 | 赤 |
| 3 | パソコン | 2 | 青 |
| 2 | ボール | 2 | 青 |
| 1 | 机 | 2 | 青 |
| 3 | パソコン | 3 | 黄 |
| 2 | ボール | 3 | 黄 |
| 1 | 机 | 3 | 黄 |

## NATURAL JOIN（自然結合）

### 機能
指定したそれぞれのテーブルの同じカラム名で結合する。
left, rightを指定すれば外部結合で、指定しない場合は内部結合で結合する。
同じカラム名がなければCROSS JOINと同じ結果となる。

### 書き方
```sql
from
  テーブル名
natural [{left|right} [outer]] join
  テーブル名
```
外部結合したい場合は left もしくは right を指定する。
outerは省略可能。
### 例

以下のテーブルから、HTTPリクエストに紐づく、レスポンスの内容を取得したい。
`request_id`というカラムが同じカラム名になっている。

- HTTPリクエストテーブル(http_requests)

|request_id|method|
|---|---|
|1|GET|
|2|POST|
|3|PUT|
|4|DELETE|

- HTTPレスポンステーブル(http_responses)

|request_id|status|
|---|---|
|1|304|
|2|200|
|3|500|

- SQL（内部結合）

```sql
select   
  *
from  
  http_requests  
natural join   
  http_responses  
;
```

- 結果（内部結合）

| request\_id | method | status |
| :--- | :--- | :--- |
| 1 | GET | 304 |
| 2 | POST | 200 |
| 3 | PUT | 500 |

- SQL（外部結合）

```sql
select   
  *
from  
  http_requests  
natural left join   
  http_responses  
;
```

- 結果（外部結合）

| request\_id | method | status |
| :--- | :--- | :--- |
| 1 | GET | 304 |
| 2 | POST | 200 |
| 3 | PUT | 500 |
| 4 | DELETE | NULL |


## STRAIGHT_JOIN

### 機能
左側のテーブルが常に右側のテーブルよりも先に読み込まれる。
パフォーマンスチューニングの際に駆動表を固定したい場合に使用する。
結合方法は INNER JOIN と同じになる。
### 書き方
```sql
from
  テーブル名
straight_join テーブル名 on
  結合条件
```
### 例

以下のテーブル定義のテーブル同士を結合してみて、straight_joinを使ったときに実行計画が変化するかどうかを確認してみる。

- コメントテーブル(comments)の定義およびデータ
```sql
create table comments  
(  
  id       int          not null  primary key,
  user_id  int          not null,  
  contents varchar(255) null  
);
```

|id|user_id|contents|
|---|---|---|
|1|1|コメント1-1|
|2|1|コメント2-1|
|3|2|コメント3-2|
|4|3|コメント4-3|
|5|10|コメント5-10|

- ユーザテーブル(users)の定義およびデータ
```sql
create table users  
(  
  id   int          not null  primary key,
  name varchar(255) not null  
);
```

|id|name|
|---|---|
|1|田中|
|2|佐藤|
|3|今井|
|4|岡田|
|5|長井|

- STRAIGHT_JOINを使わずに結合したときのEXPLAINの結果

```sql
explain  
select  
  *  
from  
  users  
join comments on  
  comments.user_id = users.id  
;
```

| id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| 1 | SIMPLE | comments | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100 | NULL |
| 1 | SIMPLE | users | NULL | eq\_ref | PRIMARY | PRIMARY | 4 | try\_join\_db.comments.user\_id | 1 | 100 | NULL |


from句にユーザテーブルを指定しているが、駆動表はコメントテーブルとなっている。
（joinの場合、実行計画の上から順にアクセスされるため、コメントテーブルが駆動表となる。）
これはコメントテーブルを駆動表にすることで、内部表であるユーザテーブルと結合する際にユーザテーブルのPKをインデックスとして使用することができ、ユーザテーブルをフルスキャンする必要がなくなるため、MySQLがこのように最適化をしたと考えられる。

- STRAIGHT_JOINを使用して結合したときのEXPLAINの結果

```sql
explain  
select  
  *  
from  
  users  
straight_join comments on  
  comments.user_id = users.id  
;
```

| id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| 1 | SIMPLE | users | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100 | NULL |
| 1 | SIMPLE | comments | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20 | Using where; Using join buffer \(hash join\) |

先ほどの実行計画と異なり、ユーザテーブルが駆動表になる。
（この例では内部表をフルスキャンしており、逆にパフォーマンスが悪くなっていますが、あくまで駆動表が変わるという例としてご覧ください。）

## まとめ

MySQLで使えるJOINは以下の6つ。

### INNER JOIN（内部結合）
結合したテーブルの両方に存在する行だけを取得する。

### LEFT OUTER JOIN（左外部結合）
結合条件に合致しない場合でも先に指定したテーブルの行はすべて取得する。  
結合条件に合致しない場合、後に指定したテーブルの列はすべてNULLとなる。

### RIGHT OUTER JOIN（右外部結合）
LEFT OUTER JOINの逆。

### CROSS JOIN（クロス結合）
指定したテーブルのすべての組み合わせを取得する。

### NATURAL JOIN（自然結合）
指定したテーブルの同じカラム名で結合する。
外部結合も可能。

### STRAIGHT_JOIN
駆動表を固定する。
パフォーマンスチューニングのために使う。
結合方法はINNER JOINと同じ。

それではまた。
TomoProg

## 参考

- [MySQL JOIN句リファレンス](https://dev.mysql.com/doc/refman/8.0/ja/join.html)

- [おうちで学べるデータベースのきほん](https://www.shoeisha.co.jp/book/detail/9784798135168)

- [「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 クロス結合](https://wa3.i-3-i.info/word15316.html)

- [MySQL | 自然結合を行う(NATURAL)](https://www.javadrive.jp/mysql/join/index3.html)

- [STRAIGHT_JOINをつかったSQLチューニング](https://blog.gmo.media/sql-tuning-with-straight_join)

- [MySQL JOINアルゴリズム詳解 - kazootkr技術ノート](https://kazootkr.hatenablog.com/entry/2021/02/27/195135)