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

この記事は MySQL Advent Calendar 2023 20日目の記事です。

はじめに

MySQLのJOIN句のリファレンスを読んでいたところ、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（内部結合）

機能

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

書き方

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

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となる。

書き方

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

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の逆。

書き方

from
  テーブル名
right [outer] join テーブル名 on
  結合条件

例

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

• 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句 を使わない。

書き方

from
  テーブル名
cross join
  テーブル名

例

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

• 商品テーブル(products)

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

• 色テーブル(colors)

id	name
1	赤
2	青
3	黄

• 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と同じ結果となる。

書き方

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（内部結合）

select   
  *
from  
  http_requests  
natural join   
  http_responses  
;

• 結果（内部結合）

request_id	method	status
1	GET	304
2	POST	200
3	PUT	500

• 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 と同じになる。

書き方

from
  テーブル名
straight_join テーブル名 on
  結合条件

例

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

• コメントテーブル(comments)の定義およびデータ

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)の定義およびデータ

create table users  
(  
  id   int          not null  primary key,
  name varchar(255) not null  
);

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

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

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の結果

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句リファレンス

• おうちで学べるデータベースのきほん

• 「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 クロス結合

• MySQL | 自然結合を行う(NATURAL)

• STRAIGHT_JOINをつかったSQLチューニング

• MySQL JOINアルゴリズム詳解 - kazootkr技術ノート