3.7 内部結合と外部結合

employees テーブルには, department_id が欠損となっているレコードが1件あります. そのため, departments のどのレコードとも紐付かず, 結果からは脱落してしまいます. 社員全員を出力したいので, 部署名が紐付かないレコードであっても出力したいという場合はどうすればよいでしょうか? 単純に考えると, ON 句に条件を追加すれば良いように思えますが, LEFT JOIN 句を使うのが最も簡単です.

SELECT t1.id,  t1.name,  t2.name as department
FROM <DB>.employees t1
LEFT JOIN <DB>.departments t2
ON t1.department_id = t2.id

LEFT JOIN 句は正確には LEFT OUTER JOIN と書きますが, OUTER は省略可能です. 内部結合に対し, この結合は, 左 (外部) 結合と呼ばれます. FROM 句の後指定したテーブルが「左」テーブルで, LEFT JOIN 句の後に書かれたテーブルが「右」テーブルになります. 左結合とは, デカルト集合のうち, ON 句の条件に一致するか, 左テーブルに存在するレコードを全て残す, という意味になります. 同様に, 「右」テーブルにあるものを条件一致するかにかかわらず全て出力したい場合は, 右 (外部) 結合を RIGHT (OUTER) JOIN 句を用いて実行します. 条件に紐付かなくても左右どちらかに存在するものを全て出力したい場合の結合は完全外部結合と呼ばれ, FULL (OUTER) JOIN 句を使います10. 以上の機能は ON 句に指定した条件にのみ適用され, WHERE 句に指定した条件は外部結合・内部結合を考慮しません. つまり, 外部結合の場合は「ON 句に指定した条件または 左 (右) テーブルに存在する」という条件づけがされていることになります.

内部結合と外部結合は, 数学の集合で習ったような, ベン図, 図3.2 をイメージするとわかりやすいです.

ベン図

図 3.2: ベン図

3.7.1 COALESCE() 関数

外部結合ではしばしば欠損が発生します. 欠損をゼロで埋めるなど, 体裁を整えるのには COALESCE() 関数が便利です. 例えば今回の例では, 部署名が紐付かないレコードがあります. これを空白ではなく, 「不明」という文言に置き換えたい場合, 以下のようにします.

SELECT t1.id,  t1.name,  COALESCE(t2.name,  '不明') AS department 
FROM <DB>.employees t1
LEFT JOIN <DB>.departments t2
ON t1.department_id = t2.id

COALESCE() 関数は, 複数の引数の値のうち, 欠損でない, 最も左の値を返す関数です. よって上記のクエリでは t2.name が欠損でないレコードでは t2.name を返し, 欠損ならば ‘不明’ を返しています.

  1. デカルト積は全ての組み合わせであるのに対して, 完全外部結合は, 結合キーの欠落したレコードを残すだけであって, 結合キーの一致しないレコードは除外されることに気をつけてください.↩︎