テーブル結合(JOIN)

テーブル結合(JOIN)とは

[[正規化]] では「1つの事実は1か所にだけ」書くためにテーブルを分けました。分けたということは、画面に出すときは元の形に戻す必要があります。その操作が JOIN です。

users(顧客)      orders(注文)
| id | name |    | id | user_id | amount |
| 1  | 山田 |    | 10 | 1       | 3000   |
| 2  | 佐藤 |    | 11 | 1       | 1500   |
| 3  | 鈴木 |    | 12 | 2       |  800   |

「誰がいくら注文したか」を出すには、orders の user_id と users の id が同じ行同士をくっつけます。この「どの列が一致したら同じ行か」を指定するのが結合条件(ON句)で、ふつうは [[primary-foreign-key]] の関係にある列を使います。

内部結合(INNER JOIN)

SELECT u.name, o.amount
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;
nameamount
山田3000
山田1500
佐藤800

ここで驚くのは次の2点です。

  • 山田が2行に増えている — JOIN の結果行数は元テーブルの行数と一致しません。1対多では「多」の側の件数だけ行が増えます
  • 鈴木が消えている — INNER JOIN は両側がそろった行だけを残します

「一覧の件数が合わない」「売上が二重に計上された」の原因はたいていこの2点です。

外部結合(LEFT JOIN)

注文が1件も無い鈴木も一覧に出したい。それが LEFT OUTER JOIN です。

SELECT u.name, o.amount
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

左(FROM に書いたテーブル)の行はすべて残り、右に相手が無ければ右側の列は NULL になります。鈴木は amount が NULL の1行になります。

踏みやすい罠が2つあります。

  1. 鈴木の注文が1件と数えられる — COUNT(*) は NULL の行も「1行」として数えるためです。注文件数を数えたいなら COUNT(o.id) と右テーブルの列を指定します(NULL は数えません)
  2. WHERE に右テーブルの条件を書くと INNER JOIN に戻る — WHERE o.amount >= 1000 と書くと NULL の鈴木は条件を満たせず消えます。右テーブルへの絞り込みは ON 句に書きます

結合条件を書き忘れると直積になる

ON を書き忘れると、すべての組み合わせが作られます。これを直積(クロス結合)と呼びます。INNER JOIN に ON を必須とし構文エラーで止める製品もありますが、FROM users, orders とカンマで並べる書き方なら条件が無くてもそのまま直積になります。

1,000行 × 1,000行 = 100万行、1万行 × 1万行 = 1億行。「クエリが返ってこない」「メモリを食い潰した」事故の典型例です。つないだテーブルの数から1引いた数だけ ON 句があるかを数える癖で防げます。

種類残る行
INNER JOIN両側で条件が一致した行だけ
LEFT OUTER JOIN左を全部残し、右が無ければ NULL
RIGHT OUTER JOIN右を全部残す。LEFT で書き直せるため出番は少ない
FULL OUTER JOIN両側を全部残す。差分照合などで使う
CROSS JOIN全組み合わせ(直積)。意図して書くときだけ
SELF JOIN同じテーブルを別名で2回使う。社員と上司など

JOIN が遅くなる理由

もっとも素朴な結合アルゴリズムは、外側を1行読むごとに内側から相手を探す方式です。このとき内側の結合キーにインデックスが無いと、1行ごとに内側を全走査します。外側1万行 × 内側10万行なら比較は10億回規模です。インデックスがあれば1行あたり数回の比較で届くため、桁違いに速くなります([[データベースインデックス]])。

  • 結合キーの列にインデックスがあるか(外部キー側は自動で作られないこともある)
  • 結合の前に WHERE で絞れているか — 先に絞るほど結合対象が減る
  • 3重・4重に重ねていないか — 中間結果の行数は掛け算で膨らむ

どこで行数が膨らんだのかは推測せず [[query-execution-plan]] で確認します。

初学者向けポイント

  • ORM を使っていても JOIN から逃げられません。N+1問題の対策である eager loading の正体は「JOIN や IN でまとめて取る」ことです([[ORM]])
  • JOIN は「両方のテーブルが同じ場所にある」前提の操作です。[[シャーディングとパーティショニング]] でサーバーをまたぐと難しくなり、[[MongoDB]] では関連を埋め込む設計が基本になります。関係を何段もたどるなら [[グラフデータベース]] もあります
  • SELECT * を避け、別名を付けて u.id / o.id と列を修飾します。両テーブルに id があるとどちらの列か分からなくなります
  • 結果が想定と違ったら、まず SELECT COUNT(*) で行数を見ます。増えていれば1対多で膨らみ、減っていれば INNER JOIN で落ちている、とあたりが付きます

関連技術とのつながり

  • [[SQL]] — JOIN は SELECT 文の一部。基本構文を先に押さえる
  • [[正規化]] — 分割の代償が JOIN。非正規化とは「JOIN を減らす」判断のこと
  • [[リレーショナルデータベース]] — 外部キーでつなぎ JOIN で組み合わせるのが RDB の中核
  • [[ORM]] — 発行される JOIN を意識できるかが N+1問題の分かれ目
  • [[グラフデータベース]] — JOIN を重ねる代わりにエッジをたどる選択肢
Q: 注文が1件も無い顧客も結果に残したいとき、使う結合はどれ?
- [ ] INNER JOIN
- [x] LEFT OUTER JOIN
- [ ] CROSS JOIN
解説: INNER JOIN は両側がそろった行だけを残すため注文の無い顧客は消えます。左を全部残すのが LEFT OUTER JOIN です。

Q: 結合条件(ON句)を書き忘れると何が起きる?
- [ ] 必ず構文エラーになり実行されない
- [x] 全組み合わせ(直積)が作られ、行数が掛け算で爆発する
- [ ] 左のテーブルだけがそのまま返る
解説: 1,000行 × 1,000行なら100万行。「クエリが返ってこない」典型的な原因です。

Q: LEFT JOIN したのに右テーブルの条件を WHERE 句に書くと何が起きる?
- [x] NULL の行が条件を満たせず落ち、実質 INNER JOIN と同じ結果になる
- [ ] 右テーブルの行がすべて残るようになる
- [ ] 結合条件が自動で ON 句へ移される
解説: 右に相手がいない行は NULL のため条件を満たせず消えます。絞り込みは ON 句に書きます。