0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

はじめに

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に書いていないか確認するとよさそうです。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?