LEFT/RIGHT OUTER JOIN
全商品のレビュー状況を一覧にしてほしい、と頼まれたとします。内部結合で書くと、レビューがまだ 1 件も付いていない本が一覧から消えます。本当に知りたかったのは、その消えたほうだったりします。
左のテーブルを丸ごと残す
SQL クエリ
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 は、読み手にとって別のもの
SQL クエリ
-- レビューが無い本の 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 に右の列を書くと、外部結合が無かったことになる
SQL クエリ
-- レビューが 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を足したら、件数をもう一度見てください。左の全件が保たれているかどうかは、そこでしか分かりません。
テーブル構造
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(30)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount INT
);
INSERT INTO customers VALUES
(1, '田中'),
(2, '鈴木'),
(3, '佐藤'),
(4, '高橋'),
(5, '山田');
INSERT INTO orders VALUES
(1001, 1, 2000),
(1002, 1, 3000),
(1003, 3, 1500),
(1004, 4, 5000),
(1005, 4, 2500);期待される出力
| customer_id | customer_name | total_amount |
|---|---|---|
| 1 | 田中 | 5000 |
| 2 | 鈴木 | 0 |
| 3 | 佐藤 | 1500 |
| 4 | 高橋 | 7500 |
| 5 | 山田 | 0 |