Odysseytechlab

MySQL JOINの順序やON句 vs WHERE句の違いと挙動の変化

執筆者:

odyssey

カテゴリ:

,

はじめに

SQLでテーブルを結合するときに使うJOIN句ですが、JOINの順序や、ON句とWHERE句の使い分けによって、意図しない絞り込みが発生することがあります。私自身、LEFT JOINを使っているつもりが、WHERE句のせいでINNER JOINのような挙動になっていたという経験があります。

今回は、小さなテストテーブルを使って、JOINの順序とON句・WHERE句の違いがどのように影響するのかを丁寧に検証していきます。

MySQL テスト用テーブルの作成

CREATE TABLE users (
    id          INT PRIMARY KEY,
    name        VARCHAR(50)
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    user_id     INT,
    item        VARCHAR(50)
);

CREATE TABLE payments (
    id          INT PRIMARY KEY,
    order_id    INT,
    amount      INT
);

初期データの挿入：

INSERT INTO users VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie');

INSERT INTO orders VALUES
    (1, 1, 'Book'),
    (2, 2, 'Pen');

INSERT INTO payments VALUES
    (1, 1, 100);

この構成では、Aliceは注文と支払いあり、Bobは注文のみ、Charlieはどちらもなしです。

WHERE句で絞るとLEFT JOINが台無しに


SELECT
    u.name,
    o.item,
    p.amount
FROM
    users AS u
LEFT JOIN
    orders AS o
        ON
            u.id = o.user_id
LEFT JOIN
    payments AS p
        ON
            o.id = p.order_id
WHERE
    p.amount > 0;
name item amount
Alice Book 100

LEFT JOINを使っているように見えますが、WHERE p.amount > 0 によって p.amount がNULLの行が除外されてしまい、INNER JOINと同じような結果になります。これではLEFT JOINの意味がなくなります。

ON句に条件を書くとLEFT JOINの意味が保たれる


SELECT
    u.name,
    o.item,
    p.amount
FROM
    users AS u
LEFT JOIN
    orders AS o
        ON
            u.id = o.user_id
LEFT JOIN
    payments AS p
        ON
            o.id = p.order_id
        AND p.amount > 0;
name item amount
Alice Book 100
Bob Pen NULL
Charlie NULL NULL

このように、p.amount > 0 をON句に移すことで、LEFT JOINの「合致しなければNULL」が維持され、支払いのないBobや注文のないCharlieも結果に含まれるようになります。

JOINの順序による結果の違い

users → orders → payments（LEFT JOIN）


SELECT
    u.name,
    o.item,
    p.amount
FROM
    users AS u
LEFT JOIN
    orders AS o
        ON
            u.id = o.user_id
LEFT JOIN
    payments AS p
        ON
            o.id = p.order_id;
name item amount
Alice Book 100
Bob Pen NULL
Charlie NULL NULL

usersを起点にしているため、Charlieのように何も関連がないユーザーも結果に含まれます。

payments → orders → users（INNER JOIN）


SELECT
    p.amount,
    o.item,
    u.name
FROM
    payments AS p
INNER JOIN
    orders AS o
        ON
            p.order_id = o.id
INNER JOIN
    users AS u
        ON
            o.user_id = u.id;
name item amount
Alice Book 100

この場合、paymentsを起点にしているため、支払いがあるAliceだけが結果に含まれます。

副問い合わせにしても同じ罠がある


SELECT
    *
FROM (
    SELECT
        u.name,
        o.item,
        p.amount
    FROM
        users AS u
    LEFT JOIN
        orders AS o
            ON
                u.id = o.user_id
    LEFT JOIN
        payments AS p
            ON
                o.id = p.order_id
) AS result
WHERE
    result.amount > 0;
name item amount
Alice Book 100

LEFT JOINしていても、外側のWHERE句でNULLが除外されれば、INNER JOINのような挙動になります。やはりON句に条件を入れる方が安全です。

MySQL JOIN 今日のまとめ

  • LEFT JOINにWHERE句で条件を書くと、INNER JOINのように絞り込まれてしまう
  • ON句に絞り込み条件を入れることで、NULLを保持した結合が可能
  • JOINの起点テーブルが結果に含まれる範囲を決める
  • 副問い合わせでもWHEREで絞るとLEFT JOINの意味が消える

次回はINNER JOIN と EXISTS の使い分けとパフォーマンス差の検証をしてみたいと思います。

投稿をさらに読み込む