SQLで複雑な結合を行う際、JOINの種類や順序によって結果が意図しない形になることがあります。本記事では、LEFT JOINの後にINNER JOINを続けたときに、思わぬ絞り込みが発生するケースについて解説します。
テーブル構造とサンプルデータ
-- 一時テーブル削除
DROP TEMPORARY TABLE IF EXISTS payments;
DROP TEMPORARY TABLE IF EXISTS orders;
DROP TEMPORARY TABLE IF EXISTS users;
-- users（ユーザー）
CREATE TEMPORARY TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
-- orders（注文）
CREATE TEMPORARY TABLE orders (
id INT PRIMARY KEY,
user_id INT,
item VARCHAR(100)
);
-- payments（支払い）
CREATE TEMPORARY TABLE payments (
id INT PRIMARY KEY,
order_id INT,
amount INT
);
-- データ挿入（users）
INSERT INTO users VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
-- データ挿入（orders）
INSERT INTO orders VALUES
(101, 1, 'Book'),
(102, 2, 'Laptop'),
(103, NULL, 'Camera'), -- user_idがNULL
(104, 99, 'Chair'); -- 存在しないuser_id（99）
-- データ挿入（payments）
INSERT INTO payments VALUES
(201, 101, 3000),
(202, 102, 80000);
-- 注文103と104には支払い情報なし
この状態で、ユーザー別の注文履歴とその支払い情報を取得したいとします。
JOINの順序に注意
次のようにJOINを書くと、意図しない結果になります。
-- 想定外の絞り込みが起こる例
SELECT u.name, o.item, p.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
INNER JOIN payments p ON o.id = p.order_id;
実行結果：
+-------+--------+--------+
| name | item | amount |
+-------+--------+--------+
| Alice | Book | 3000 |
| Bob | Laptop | 80000 |
+-------+--------+--------+
今回欲しい情報としてはユーザー別の注文履歴なのでCharlieの情報も欲しいです。しかし表の方の実行結果ではCharlieやその他の注文が表示されていませでした。これは、INNER JOIN paymentsが全体にかかっており、ordersにマッチしなかった行が除外されてしまうためです。
意図通りにするには
JOINの順序を制御するには、LEFT JOIN全体を括弧で囲み、サブクエリ風に扱うことで対応できます。
-- 正しく全データを保持する形
SELECT u.name, o.item, p.amount
FROM (
users u
LEFT JOIN orders o ON u.id = o.user_id
)
LEFT JOIN payments p ON o.id = p.order_id;
または、以下のようにJOINの入れ子で構成しても良いでしょう：
SELECT u.name, o.item, p.amount
FROM users u
LEFT JOIN (
orders o
LEFT JOIN payments p ON o.id = p.order_id
) ON u.id = o.user_id;
補足: 実務では、JOINの順序や種類を明確にするために、括弧を使って結合範囲を明示するのが一般的です。これは人間の可読性だけでなく、SQL解析ツールやBIツール、ORMなどのソフトウェアでも意図を正確に伝えるために重要です。
補足2: パフォーマンスの面では、INNER JOIN を中心に構成されたクエリの方が MySQL の最適化が効きやすい傾向があります。LEFT JOIN を多用すると、結合の順序やNULLの扱いでオプティマイザが制限を受ける場合があるため、大規模データではEXPLAINや実行時間の比較も検討しましょう。
補足3: 実務上、JOINを入れ子にして記述すると結合の範囲が明示されるため、結果の予測がしやすく、バグの防止にもつながります。構文として括弧を使うことで、JOINの優先順位を人間にもソフトにも分かりやすく示せます。なお、入れ子のJOINはINNER JOINが中心となるため、パフォーマンスの面でも有利な場合があります。（By ChatGPT） 私自身はこれまで実務で明示的な入れ子JOINを使う機会がありませんでした。可動性やRDBMSの種類によっては読み取り内容が異なるようなので積極的に使っていこうと思います。
まとめ
- JOINの種類だけでなく、結合の順序も結果に大きく影響する
LEFT JOIN → INNER JOINは予期せぬ絞り込みを引き起こすことがある- サブクエリや括弧を使って結合範囲を明確にすることで防げる
- 特に実務では明示的に書く方がバグや可読性の観点からも推奨される
次回は、JOINの入れ子や副問い合わせを活用した柔軟な絞り込み手法を紹介します。