はじめに
LEFT JOINで条件を書くとき、次の2つは同じように見えます。
LEFT JOIN orders AS o
ON u.id = o.user_id
AND o.status = 'paid'
LEFT JOIN orders AS o
ON u.id = o.user_id
WHERE o.status = 'paid'
しかし、実行結果は同じになりません。
今回は小さなテーブルを使って、ONとWHEREに条件を置いた場合の違いを確認します。
テーブルを作る
ユーザーを4人登録します。
CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO users (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol'),
(4, 'Dave');
注文テーブルには、支払い済みのpaidと未処理のpendingを登録します。
CREATE TABLE orders (
id INT NOT NULL,
user_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO orders (id, user_id, status) VALUES
(101, 1, 'paid'),
(102, 1, 'pending'),
(103, 2, 'pending'),
(104, 3, 'paid');
データの状態は次のとおりです。
| ユーザー | 注文 |
|---|---|
| Alice |
paidとpending
|
| Bob |
pendingのみ |
| Carol |
paidのみ |
| Dave | 注文なし |
条件をONに書く
まずは、paidという条件をONに書きます。
SELECT
u.id AS user_id,
u.name,
o.id AS order_id,
o.status
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
AND o.status = 'paid'
ORDER BY u.id;
結果は次のようになります。
user_id | name | order_id | status
--------+-------+----------+-------
1 | Alice | 101 | paid
2 | Bob | NULL | NULL
3 | Carol | 104 | paid
4 | Dave | NULL | NULL
ONの条件は、右側のテーブルからどの行を結合するかを決めます。
Bobにはpendingの注文しかないため、条件に一致する注文はありません。しかし、LEFT JOINなのでBob自身の行は残り、注文側の値がNULLになります。
注文がないDaveも同様に残ります。
MySQLでは、LEFT JOINの右側に一致する行がない場合、右側の各カラムをNULLとした行が生成されます。また、通常はONが結合方法を、WHEREが結果セットに含める行を指定します。
条件をWHEREに書く
次は、paidという条件をWHEREに移します。
SELECT
u.id AS user_id,
u.name,
o.id AS order_id,
o.status
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY u.id;
結果は次のようになります。
user_id | name | order_id | status
--------+-------+----------+-------
1 | Alice | 101 | paid
3 | Carol | 104 | paid
BobとDaveが消えました。
LEFT JOINによって注文側がNULLになった行では、次の条件はTRUEになりません。
o.status = 'paid'
そのため、WHEREで行が除外されます。
MySQLでは、このようにNULL補完行に対してFALSEまたはUNKNOWNになる条件を、null-rejectedな条件として扱います。この場合、オプティマイザはLEFT JOINをINNER JOINへ変換できます。
違いをまとめる
| 条件を書く場所 | 意味 | 注文がないユーザー |
|---|---|---|
ON |
結合する注文を絞る | 残る |
WHERE |
結合後の結果を絞る | 消える |
「全ユーザーを表示し、支払い済み注文だけを横に付けたい」なら、条件はONに書きます。
LEFT JOIN orders AS o
ON u.id = o.user_id
AND o.status = 'paid'
「支払い済み注文が存在するユーザーだけを表示したい」なら、WHEREでも取得できます。
LEFT JOIN orders AS o
ON u.id = o.user_id
WHERE o.status = 'paid'
ただし、この目的なら最初からINNER JOINと書いた方が意図が分かりやすいです。
INNER JOIN orders AS o
ON u.id = o.user_id
AND o.status = 'paid'
まとめ
LEFT JOINでは、同じ条件でも置く場所によって結果が変わります。
-
ONに書くと、右側から結合する行を絞る -
WHEREに書くと、結合した後の結果全体を絞る - 右側テーブルの条件を
WHEREに書くと、NULL行が消える - 結果として、
LEFT JOINが実質的にINNER JOINのように動くことがある
LEFT JOINを使ったのに左側の行が消えたときは、右側テーブルの条件をWHEREに書いていないか確認するとよさそうです。