SQL の結合

SQL の結合は条件で二つの関係の行を組み合わせる。等値結合では、各左行に対して比較が TRUE となる全右行を組にし、結合種類が未一致行の扱いを決める。重複キーは結果を増やし、相手のない行は消えるか NULL で補われる。

組み合わせる単位は行で、異なるキー値ではない。同じキーに左三行、右三行なら九組になる。通常は重複出力の不具合ではなく、条件が許した組だ。

SQL 結合の仕組み

従業員と事務所を部署番号で結ぶ例を使う。

SELECT e.employee, e.dept_id, o.dept_id, o.city
FROM employees AS e
LEFT JOIN offices AS o
  ON e.dept_id = o.dept_id;

非 NULL キー k の左群を L_k、右群を R_k とすると、一致行は |L_k| × |R_k| 個。多重集合の意味論なので、同キーの二行も二行のまま参加し、自動で重複除去しない。

• INNER JOIN は一致した組だけを残す。
• LEFT JOIN は未一致左行も残し、右列を NULL にする。
• FULL OUTER JOIN は未一致右行も残し、左列を NULL にする。

これは論理結果の規則だ。実行は入れ子ループ、ハッシュ、マージ、索引探索などでよく、組み合わせ行列を物理的に作る約束ではない。

SQL 結合のステップ

既定の employees は Ada/10、Bo/20、Cy/30、Dee/40。offices は 10 に NYC と London、30 に Paris、50 に Tokyo を持つ。LEFT JOIN を実行する。

1. Ada の 10 は二事務所と合い、1 × 2 = 2。(Ada, 10, 10, NYC) と (Ada, 10, 10, London) を返す。一つを選ばせない。
2. Bo の 20 は相手がなく、(Bo, 20, NULL, NULL) を残す。
3. Cy の 30 は一つと合い、(Cy, 30, 30, Paris)。
4. Dee の 40 は合わず、(Dee, 40, NULL, NULL)。
5. Tokyo の 50 は右だけにあり、LEFT JOIN は保護しないので返さない。

計五行で、一致三組と左の欠け二行になる。画面は元表に沿った説明順を使うが、ORDER BY のない SQL はその返却順を保証しない。

行数が増える理由

既定の INNER JOIN は三行、LEFT は Bo と Dee を加えて五行、FULL は Tokyo も加えて六行。多対多プリセットは同じ 10 に従業員三人、事務所三つがあり、一キーだけで 3 × 3 = 9 行になり、どちらの入力よりも大きくなりうる。

調査は両側のキーごとの件数から始め、最初から DISTINCT で隠さない。左 12、右 8 なら 96 組だ。重複除去だけでは欠けた制約や不完全な ON 条件を隠してしまう。

• 全ての適合組が必要なら結合を使う。
• 相手が存在するかだけなら EXISTS を使う。
• 左一行に右一行が必要なら、唯一制約、厳しい条件、順位、集約で選ぶ規則を定める。
• 左の未一致を残すなら LEFT、一致だけなら INNER、両側を残す必要があるなら FULL OUTER を選ぶ。

境界ケース

• 共通キーなしでは INNER はゼロ行、LEFT は左、FULL は両側を NULL 補完で残す。
• 通常の = で NULL = NULL は TRUE ではなく unknown。二つの NULL キーは合わない。
• 同キーの左二行と右四行は八組で、それぞれ独立して参加する。
• 同キーに未一致が複数あれば、一つの枠へ圧縮せず各行を残す。
• 方言の対応は同一でなく、FULL OUTER JOIN や書き換えの前に対象の構文を確認する。

よくある間違い

• キーごとに一行と期待する。実際は条件を満たす行の組が単位だ。
• 増加原因の前に DISTINCT を足し、不足する制約の証拠を消す。
• LEFT JOIN 後に WHERE o.city = 'Paris' を置く。NULL 補完行が除かれ、内結合のようになりうる。一致条件なら ON、完成結果の絞り込みなら WHERE を使う。
• NULL 同士が等号で合うと期待する。意図するなら対象 DB の NULL 対応比較を使う。
• 表示順を結合の意味と読む。順序は ORDER BY が指定する。
• 論理的な組み合わせ行列を性能トレースと読む。最適化器はその実体化を省ける。

モデルの範囲

等値結合、多重集合、未一致行の保存、NULL 補完を扱う。射影の書き換え、条件の押し下げ、索引、件数推定、計画、可視性、照合規則、物理アルゴリズムは省く。実行方法とは分けて、ここでは三結合の論理結果を固定している。