JOINとLEFT JOINの違いをこれまであまりきちんと理解していなかったので改めておさらい。
結論
簡単に結論だけ先にまとめておくと
- JOINの場合、合致する右表のレコードがない場合は対応する左表のレコードが削除される(結果に出力されない)
- LEFT JOINの場合、合致する右表のレコードがない場合でも対応する左表のレコードが残る(結合条件のカラムはNULLの状態で出力される)
細かく見ていく。
JOINとLEFT JOINの違い
例えば以下のテーブルがあった場合
mysql> select id, client_id from users; +----+-----------+ | id | client_id | +----+-----------+ | 1 | NULL | | 5 | NULL | | 6 | NULL | | 7 | NULL | | 8 | NULL | | 2 | 1 | | 9 | 1 | | 3 | 2 | | 4 | 3 | +----+-----------+ 9 rows in set (0.02 sec) mysql> select id, name from clients; +----+------------+ | id | name | +----+------------+ | 1 | ?????????1 | | 2 | ?????????2 | | 3 | ?????????3 | +----+------------+ 3 rows in set (0.00 sec)
(nameが文字化けしてるけど直すの面倒なのでそのまま載せるw)
usersテーブルにはclient_idをもつレコードとNULLのレコードが存在する。
クライアント名を参照したいなーと思った場合、clientsテーブルをjoinして結合すれば参照できるのだけど
単純なJOINだと、以下のように「userテーブルのうちclient_idがNULLのレコード」はselectの結果から除外される。
mysql> select users.id, users.client_id, clients.name from users join clients on users.client_id = clients.id; +----+-----------+------------+ | id | client_id | name | +----+-----------+------------+ | 2 | 1 | ?????????1 | | 9 | 1 | ?????????1 | | 3 | 2 | ?????????2 | | 4 | 3 | ?????????3 | +----+-----------+------------+ 4 rows in set (0.00 sec)
(ちなみにjoinはinner joinと挙動同じ)
対して、LEFT JOINの場合
mysql> select users.id, users.client_id, clients.name from users left join clients on users.client_id = clients.id; +----+-----------+------------+ | id | client_id | name | +----+-----------+------------+ | 2 | 1 | ?????????1 | | 9 | 1 | ?????????1 | | 3 | 2 | ?????????2 | | 4 | 3 | ?????????3 | | 1 | NULL | NULL | | 5 | NULL | NULL | | 6 | NULL | NULL | | 7 | NULL | NULL | | 8 | NULL | NULL | +----+-----------+------------+ 9 rows in set (0.01 sec)
client_idがNULLのレコード(左表のレコード)も削除されない。
結合の条件となったclient_idカラムやclientsテーブルのカラム(nameなど)はNULLとして結合され、左表のusersテーブルのレコードもそのまま残った状態で出力される。
単純に結合されたレコードのみ欲しい場合は単純なJOINを使えば良くて、紐づくクライアントがいるかどうかで処理の出し分けをしたい場合とかはLEFT JOINを使用する、みたいな使い分けになりそう。
LEFT JOINとRIGHT JOINの違い
LEFT JOINがあるならRIGHT JOINもあり、この場合右表のレコードが全て保持されるので、反対に左表のレコードがNULLのものが発生する。
ここまでのところを日本語で整理すると、
- JOIN...内部結合
- LEFT JOIN...左外部結合
- RIGHT JOIN...右外部結合
この他に左右どちらも残す完全外部結合というのもあるけど、これ使うシーンってあるのか?クエリとしてものすごく非効率な気がする。。
まとめ
- 内部結合...結合相手が見つからない場合に左表のレコードが削除される
- 外部結合...結合相手が見つからなくてもレコードが削除されない
- 左外部結合...結合相手が見つからなくても左表のレコードが削除されない(結合条件と右表のカラムは全てNULL)
- 右外部結合...結合相手が見つからなくても右表のレコードが削除されない(結合条件と左表のカラムは全てNULL)
参考
スッキリわかるSQL入門 第2版 ドリル222問付き! スッキリわかるシリーズ | 中山清喬, 飯田理恵子 | 工学 | Kindleストア | Amazon