SQL 複数カラムでのJOIN -- 複合結合条件

複数カラムでの結合

単一のカラムではテーブル間の行を一意にマッチさせるのに十分でない場合があります。複合結合条件はON句で2つ以上のカラムを使用します。

構文

SELECT s.student_name, g.grade
FROM students AS s
INNER JOIN grades AS g
  ON s.student_id = g.student_id
  AND s.semester = g.semester;

行ペアがマッチするには両方の条件が真でなければなりません。これは論理的なANDです。

複数カラム結合が必要な場合

1. 複合主キー：複数カラムの主キー（例：student_id + semester）を持つテーブルは、結合にすべてのキーカラムが必要です。
2. パーティション化されたデータ：時間でパーティション化されたテーブルは、エンティティIDに加えて日付カラムが必要なことが多い。
3. マルチテナントシステム：tenant_idカラムがビジネスキーと共に結合され、テナント間のデータ漏洩を防ぎます。
4. バージョン管理レコード：特定のバージョンを取得するためにentity_idとversionの両方で結合。

結合条件のAND vs OR

-- AND：両方がマッチする必要がある（厳密）
ON a.x = b.x AND a.y = b.y

-- OR：いずれかがマッチすればよい（広範）
ON a.x = b.x OR a.y = b.y

結合条件でのORはまれで、結果セットを劇的に増加させます。データベースは単純なインデックスシークを使用できず、両方のカラムを独立にスキャンして結果をユニオンする必要があるかもしれません。

インデックス戦略

複合結合条件には、すべての結合カラムをカバーする複合インデックスを作成します：

CREATE INDEX idx_grades_student_semester
  ON grades (student_id, semester);

インデックスのカラム順序は結合条件の順序と一致させるべきです。student_idのみの単一カラムインデックスでも役立ちますが、semesterを追加すると結合のためのカバリングインデックスになります。

よくある間違い：部分的な結合

結合カラムの1つを忘れると、予期しない重複行が発生する可能性があります。複合キーに3つのカラムがある場合、3つすべてがON句に表示される必要があります。1つを省略すると暗黙的にマッチが広がります。