LEFT/RIGHT OUTER JOIN

全商品のレビュー状況を一覧にしてほしい、と頼まれたとします。内部結合で書くと、レビューがまだ 1 件も付いていない本が一覧から消えます。本当に知りたかったのは、その消えたほうだったりします。

左のテーブルを丸ごと残す

SELECT b.title, r.rating
FROM books b
LEFT JOIN reviews r
  ON b.id = r.book_id;

LEFT JOIN は FROM に書いた左のテーブルの行を全部残します。右に相手が見つからなかった行は、右側の列が NULL で埋まります。OUTER は省略でき、LEFT OUTER JOIN と LEFT JOIN は同じものです。

RIGHT JOIN は左右が入れ替わるだけです。実務では主軸のテーブルを左に置いて LEFT JOIN で広げる形に統一するのが読みやすく、RIGHT JOIN が混ざったクエリはレビューで指摘されがちです。

空欄と 0 は、読み手にとって別のもの

-- レビューが無い本の avg_rating は NULL になる
SELECT b.title, AVG(r.rating) AS avg_rating
FROM books b
LEFT JOIN reviews r ON b.id = r.book_id
GROUP BY b.id, b.title;

集計の対象になる行が 1 つも無いと、AVG も SUM も 0 ではなく NULL を返します。レポートに空欄が並ぶと、集計漏れなのか本当に 0 なのかが読み手に伝わりません。COALESCE(AVG(r.rating), 0) のように包んで 0 に置き換えておくと、意図がはっきりします。

同じ理由で、件数の数え方にも注意が要ります。COUNT(*) は行そのものを数えるので、右が NULL で埋まっただけの行も 1 件として数えます。レビューが 0 件の本が「1 件」と出てしまうわけです。右のテーブルの列を指定して COUNT(r.id) と書けば、NULL は数えられず 0 になります。

WHERE に右の列を書くと、外部結合が無かったことになる

-- レビューが 1 件も付いていない本を探す
SELECT b.title
FROM books b
LEFT JOIN reviews r ON b.id = r.book_id
WHERE r.id IS NULL;

これは意図どおりに動きます。右が空だった行を狙って拾っているからです。一方で WHERE r.rating >= 4 のような普通の条件を右の列に書くと、右側が NULL の行は条件に合わずに全部落ちます。結果は内部結合とまったく同じになり、LEFT JOIN と書いた意味が消えます。

左を全部残したまま右に条件を付けたいときは、その条件を ON 句の側へ移します。

解説

LEFT JOIN に WHERE を足したら、件数をもう一度見てください。左の全件が保たれているかどうかは、そこでしか分かりません。