SQLのJOINにはINNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOINなど複数の種類があるが、ぶっちゃけ私はいつもどれがどんな挙動になるかすぐ忘れてしまっていた。忘れるたびに調べていたが、これ、世の中の説明の仕方が悪いのが原因だと思ってる。それぞれの挙動を自分で解釈し直した結果、忘れない様になった。その解釈の仕方を説明する。
❌ 誤ったイメージ
まず、JOIN 全般の挙動について、結合元のテーブルの行を順番に見ていってその各行について条件に当てはまる結合先のテーブルの行を結合していくというイメージをよく持たれるが、このイメージはあまり正確ではない。このイメージを持っていると挙動が逆にわからなくなるので忘れた方がいい。
✅ 全ての基礎は CROSS JOIN
すべての JOIN の基礎は CROSS JOIN である。これが最も重要な考え方だと思う。CROSS JOIN は2テーブル間の全ての行の組み合わせを返す。例えば上の二つのテーブルの CROSS JOIN は以下になる。
SELECT * FROM items CROSS JOIN categories;
| items.id | items.name | items.category_id | categories.id | categories.name |
|---|---|---|---|---|
| 1 | iPhone15 | 2 | 1 | パソコン |
| 1 | iPhone15 | 2 | 2 | スマホ |
| 1 | iPhone15 | 2 | 3 | タブレット |
| 1 | iPhone15 | 2 | 4 | イヤホン |
| 2 | iPad | 3 | 1 | パソコン |
| 2 | iPad | 3 | 2 | スマホ |
| 2 | iPad | 3 | 3 | タブレット |
| 2 | iPad | 3 | 4 | イヤホン |
| 3 | Pixel9 | 2 | 1 | パソコン |
| 3 | Pixel9 | 2 | 2 | スマホ |
| 3 | Pixel9 | 2 | 3 | タブレット |
| 3 | Pixel9 | 2 | 4 | イヤホン |
| 4 | Let's Note | 1 | 1 | パソコン |
| 4 | Let's Note | 1 | 2 | スマホ |
| 4 | Let's Note | 1 | 3 | タブレット |
| 4 | Let's Note | 1 | 4 | イヤホン |
| 5 | Apple Watch | NULL | 1 | パソコン |
| 5 | Apple Watch | NULL | 2 | スマホ |
| 5 | Apple Watch | NULL | 3 | タブレット |
| 5 | Apple Watch | NULL | 4 | イヤホン |
上の例では5行のテーブルと4行のテーブルを結合したので5 × 4 = 20行出力されている。
またこの仕組みから分かる様に CROSS JOIN には左右のテーブルに差はない。JOIN句前後のテーブルを入れ替えても結果は同じである。
CROSS JOIN そのものは実用性が低いが、これは他の種類の JOIN を考える上で基礎となる JOIN なので非常に重要である。
✅ CROSS JOIN を条件でフィルタする INNER JOIN
CROSS JOIN を元にすれば INNER JOIN は簡単に理解できる。INNER JOIN は CROSS JOIN の結果の行を条件でフィルタリングしているだけである。INNER JOIN の例は以下になる。
SELECT * FROM items INNER JOIN categories ON items.category_id = categories.id;
| items.id | items.name | items.category_id | categories.id | categories.name |
|---|---|---|---|---|
| 1 | iPhone15 | 2 | 2 | スマホ |
| 2 | iPad | 3 | 3 | タブレット |
| 3 | Pixel9 | 2 | 2 | スマホ |
| 4 | Let's Note | 1 | 1 | パソコン |
この結果は前節の CROSS JOIN の結果の行のうち、ON句の後に指定した条件items.category_id = categories.idを満たすものだけを抽出したものであることがわかる。
上は最も一般的な使用例だったが、ON句の後の条件は好きなように指定できる。例えば以下の様なこともできる。
SELECT * FROM items INNER JOIN categories ON (items.name LIKE 'i%' AND categories.id > 2);
| items.id | items.name | items.category_id | categories.id | categories.name |
|---|---|---|---|---|
| 1 | iPhone15 | 2 | 3 | タブレット |
| 2 | iPad | 3 | 3 | タブレット |
| 1 | iPhone15 | 2 | 4 | イヤホン |
| 2 | iPad | 3 | 4 | イヤホン |
この結果にはあまり実用性がないが、INNER JOIN の仕組みが理解できると思う。
仕組みから分かるように INNER JOIN にも左右のテーブルに差はない。
上の結果の通り左側のテーブルでも重複が起きうることからも誤ったイメージの説明だと結果が解釈できないことがわかる。
✅ INNER JOIN に0行の場合の特別処理を追加する OUTER JOIN
OUTER JOIN は INNER JOIN の結果にさらに特別な処理を追加したものである。特別な処理とは「結合前のテーブルの行のうち INNER JOIN 後の結果に一つも存在しないものがあったらその行を追加する」というものである。例えば LEFT OUTER JOIN は結合前の左側のテーブルの行のうち INNER JOIN 後の結果に一つも存在しないものがあったらその行を追加する。
SELECT * FROM items LEFT OUTER JOIN categories ON items.category_id = categories.id;
| items.id | items.name | items.category_id | categories.id | categories.name |
|---|---|---|---|---|
| 1 | iPhone15 | 2 | 2 | スマホ |
| 2 | iPad | 3 | 3 | タブレット |
| 3 | Pixel9 | 2 | 2 | スマホ |
| 4 | Let's Note | 1 | 1 | パソコン |
| 5 | Apple Watch | NULL | NULL | NULL |
上の通り items テーブルの行のうち Apple Watch は INNER JOIN の結果になかったので追加されている。追加された行の相手側のテーブルのカラムは NULL になる。
RIGHT OUTER JOIN は左右のテーブルが逆になっただけ。
SELECT * FROM items RIGHT OUTER JOIN categories ON (items.category_id = categories.id);
| items.id | items.name | items.category_id | categories.id | categories.name |
|---|---|---|---|---|
| 1 | iPhone15 | 2 | 2 | スマホ |
| 2 | iPad | 3 | 3 | タブレット |
| 3 | Pixel9 | 2 | 2 | スマホ |
| 4 | Let's Note | 1 | 1 | パソコン |
| NULL | NULL | NULL | 4 | イヤホン |
上では categories テーブルの行のうちイヤホンが INNER JOIN の結果に一つもなかったので追加されている。スマホは2行それ以外は1行含まれるが、ここでの処理は0行の場合1行追加するというだけなのでこうなる。
FULL OUTER JOIN は LEFT OUTER JOIN と RIGHT OUTER JOIN の両方の処理を行う。
SELECT * FROM items FULL OUTER JOIN categories ON (items.category_id = categories.id);
| items.id | items.name | items.category_id | categories.id | categories.name |
|---|---|---|---|---|
| 1 | iPhone15 | 2 | 2 | スマホ |
| 2 | iPad | 3 | 3 | タブレット |
| 3 | Pixel9 | 2 | 2 | スマホ |
| 4 | Let's Note | 1 | 1 | パソコン |
| 5 | Apple Watch | NULL | NULL | NULL |
| NULL | NULL | NULL | 4 | イヤホン |
✏️ 仕上げ
最後にこれまでを踏まえて以下のクエリの結果がどうなるか考えてみる。
SELECT * FROM items RIGHT OUTER JOIN categories ON (items.name LIKE 'i%' AND categories.id > 2);
正解は以下である。
| items.id | items.name | items.category_id | categories.id | categories.name |
|---|---|---|---|---|
| NULL | NULL | NULL | 1 | パソコン |
| NULL | NULL | NULL | 2 | スマホ |
| 1 | iPhone15 | 2 | 3 | タブレット |
| 2 | iPad | 3 | 3 | タブレット |
| 1 | iPhone15 | 2 | 4 | イヤホン |
| 2 | iPad | 3 | 4 | イヤホン |
あとがき
JOIN の動作は誤ったイメージで示したようにイメージされることが多いが（私もそうだった）、このイメージだと例えば上の例の場合の挙動などが理解できなくなる。JOIN は CROSS JOIN から派生していくものとして捉えると全てがスッキリ理解できて、この場合どうなるんだっけ？となる事がない。
この誤ったイメージは SQL の文法のせいという部分が大きいと思っている。SELECT * FROM A JOIN B という書き方だと A と B が等価には見えない。見た感じ A を基準にして B を結合します、って感じを出しているのに実は二つは等価で全部の組み合わせを考えているというのが直感的じゃない。今考えると JOIN 句を二つのテーブルの直積をとる演算子のように捉えれば良いのかもしれない。