クエリの中にクエリを書く - サブクエリ

スカラー・行・表サブクエリと相関サブクエリ、EXISTS / IN の使い分け、NOT IN の NULL 罠

概念図

構文

sql

(SELECT ...) を SELECT / FROM / WHERE / ON の式として利用

サンプル

外側の u.id を参照する相関サブクエリで各行ごとに最新注文日を取得

sql

-- 相関サブクエリ: 各ユーザーの最新注文日
SELECT u.id, u.name,
  (SELECT MAX(o.created_at)
   FROM orders o
   WHERE o.user_id = u.id) AS last_order_at
FROM users u;

サブクエリの種類

スカラーサブクエリ: 1 行 1 列を返し、値 1 つとして扱える。SELECT リストや WHERE の右辺に置く
行サブクエリ: 1 行だが複数列。WHERE (a, b) = (SELECT ...) のように使う
表サブクエリ（派生テーブル）: 複数行複数列。FROM に置いて仮想テーブル扱い

相関サブクエリ

サブクエリの中で外側のクエリの列を参照するものを相関サブクエリといいます。外側の各行ごとにサブクエリが評価されるため、直感的ですが遅くなりやすい点に注意してください。

多くの場合、集約 + JOIN やウィンドウ関数に書き換えることでパフォーマンスが大きく改善します。

sql

-- 相関サブクエリ版 (遅くなりがち)
SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS n
FROM users u;

-- JOIN + 集約に書き換え
SELECT u.id, COALESCE(agg.n, 0) AS n
FROM users u
LEFT JOIN (SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id) agg
  ON agg.user_id = u.id;

EXISTS vs IN

「〜が存在する」条件には EXISTS と IN が使えます。

EXISTS: 相関サブクエリ形式。1 件見つかれば打ち切るので最適化されやすい。NULL に強い
IN: 値リスト／非相関サブクエリに自然。行が少ないとシンプルで速い
現代のオプティマイザでは両者のパフォーマンス差は小さいことが多い。意味が明確な方を選ぶのが第一

sql

-- 注文があるユーザーを取得
SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

SELECT u.*
FROM users u
WHERE u.id IN (SELECT user_id FROM orders);

落とし穴: NOT IN + NULL

サブクエリ結果に NULL が 1 件でも混ざると x NOT IN (...) は常に UNKNOWN になり、外側クエリは一行も返しません。3 値論理により x <> NULL が UNKNOWN になるためです。

対策は次のどちらか。

NOT EXISTS に書き換える（推奨）
サブクエリに WHERE col IS NOT NULL を追加して NULL を除外する

sql

-- ハマりやすい
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

-- 安全
SELECT u.*
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);