IN と EXISTS・相関サブクエリの書き換え

本記事で使うデータ — perf_sales と employee

同じ結果を返すクエリでも、書き方によって実行計画（データベースがどの順でテーブルを読み、索引を使うかを決めた手順）が変わります。

本記事ではIN と EXISTS の使い分け、相関サブクエリ（外側の行ごとに内側を評価し直すサブクエリ）の除去、そして「一致がある／ない行だけを残す」書き方の書き換えを、EXPLAIN QUERY PLANで計画を見比べながら学びます。

演習に入る前に、本記事で使う 2 つのテーブル — perf_salesとemployee — の列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(perf_sales);とPRAGMA table_info(employee);で両テーブルの列定義を確認してください。

② SELECT * FROM perf_sales LIMIT 5;とSELECT * FROM employee LIMIT 5;で先頭 5 行のデータをプレビューしてください。perf_salesは 5 万行あるので、必ずLIMITを付けて確認してください。

IN と EXISTS — 同じ判定を 2 通りで書く

「別テーブルに一致する行があるか」を問う書き方は 2 通りあります。

WHERE 列 IN (SELECT ...)は内側が返す値の集合に含まれるかを判定し、WHERE EXISTS (SELECT 1 FROM ... WHERE ...)は内側が 1 行でも返すかを判定します。

多くの場合どちらも同じ結果になり、データベースは両方を一致がある行だけを残す処理として扱えます。

EXPLAIN QUERY PLAN クエリ;を先頭に付けると、データベースが選んだ手順が行で表示されます。

SCAN perf_salesは全行走査、SEARCH ... USING INDEXは索引で必要な行へ直接到達、CORRELATED SCALAR SUBQUERYは外側の行ごとに評価し直すサブクエリを表します。

-- 例: dept_id=2 の社員が担当した売上の件数を 2 通りで

-- IN 形式
SELECT COUNT(*) FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 2);

-- EXISTS 形式 (相関あり)
SELECT COUNT(*) FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 2
);

-- 計画を見るには先頭に EXPLAIN QUERY PLAN を付ける
-- EXPLAIN QUERY PLAN SELECT COUNT(*) FROM perf_sales WHERE ...;

もう少し具体的に、外側 (outer) のテーブル規模と内側 (inner) のサブクエリ規模・索引の有無で IN と EXISTS の処理がどう変わるかを見比べます。

次の 2 つのケースについて、IN と EXISTS のそれぞれの処理とどちらが速いかを下の図で整理します。

-- ケース A: 外側 perf_sales (5 万行), 内側 dept_id=1 の社員 (~5 行・小さく固定)

-- IN 形式
SELECT * FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 1);

-- EXISTS 形式
SELECT * FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 1
);

-- ケース B: 外側 employee (30 行), 内側 perf_sales (5 万行・emp_id に索引あり)

-- IN 形式
SELECT * FROM employee e
WHERE e.emp_id IN (SELECT emp_id FROM perf_sales);

-- EXISTS 形式
SELECT * FROM employee e
WHERE EXISTS (
  SELECT 1 FROM perf_sales p
  WHERE p.emp_id = e.emp_id
);

Tips — 最近のデータベースでは実速度差はほぼない

ここまで「IN が向く / EXISTS が向く」と分けて整理しましたが、これは古い最適化器を前提とした傾向です。

SQLite / PostgreSQL / MySQL 8.0 以降など現代のデータベースでは、IN (SELECT ...) と相関EXISTSはどちらも同じ「一致がある行だけを残す処理」に変換され、実行計画と実速度はほぼ同じになります。

つまり、書き方の好み(可読性)で選んで構いません。

計画の差や速度差が気になるときは、実測(EXPLAIN QUERY PLANと所要時間)で確かめるのが原則です。

「dept_idが 2 の社員が担当したperf_salesの件数」を、IN 形式と EXISTS 形式の 2 通りで書き、それぞれの実行計画を比較します。（エラーなく実行できれば解説が表示されます）

① EXPLAIN QUERY PLANを先頭に付けて、emp_id IN (SELECT ...)形式の件数クエリの計画を表示してください。

② 続けて、EXISTS形式（employeeを相関させる）で同じ件数を求めるクエリの計画もEXPLAIN QUERY PLANで表示してください。

③ 2 つの計画に出てくるSCAN / SEARCH / USING INDEXなどの語を見比べてください。

相関サブクエリを JOIN・集約に書き換える

SELECT リストに置いた相関サブクエリ（外側の各行ごとに評価し直すサブクエリ）は、行数が多いと評価回数が膨らみます。

EXPLAIN QUERY PLANではCORRELATED SCALAR SUBQUERYという行で現れます。

多くの場合、これは1 回の集約をあらかじめ作って JOIN する形へ書き換えられ、外側の行ごとの再評価がなくなります。

書き換えの型は「外側の行ごとに(SELECT ... WHERE 子.キー = 親.キー)を呼ぶ」を、「GROUP BY キーで集計表を 1 回作り、それをJOINで親に結合する」に変えるものです。

結果は同じで、計画から相関の再評価が消えます。

-- 書き換え前: 社員ごとの売上合計を相関サブクエリで
SELECT e.emp_id, e.name,
  (SELECT SUM(p.amount) FROM perf_sales p
   WHERE p.emp_id = e.emp_id) AS total
FROM employee e
ORDER BY e.emp_id;

-- 書き換え後: 1 回の集約を作って JOIN
WITH sales_sum AS (
  SELECT emp_id, SUM(amount) AS total
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.name, s.total
FROM employee e
LEFT JOIN sales_sum s ON s.emp_id = e.emp_id
ORDER BY e.emp_id;

「各社員のperf_sales売上合計の一覧」を、相関サブクエリ版と書き換え版の 2 通りで実行し、計画の違いを確認します。

① EXPLAIN QUERY PLANを先頭に付けて、SELECT リストに(SELECT SUM(amount) FROM perf_sales WHERE 同じ emp_id)を相関で書いた版の計画を表示してください。

② 同じ集計をemp_idで 1 回だけGROUP BYした CTEにまとめ、employeeに LEFT JOIN する版を書き、その計画もEXPLAIN QUERY PLANで表示してください。

③ 相関版の計画に出るCORRELATED SCALAR SUBQUERYが、書き換え版では消えていることを見てください。

NOT IN の NULL の落とし穴 — NOT EXISTS で回避する

「一致する行がない」を問う反結合では、NOT IN (SELECT ...)に落とし穴があります。

内側の集合に NULL が 1 つでも混じると、NOT INは 3 値論理（真・偽・不明の 3 状態で評価する論理）により全体が不明になり、結果が 0 行になってしまいます。

一方NOT EXISTS (SELECT 1 ... WHERE ...)は内側に行があるかだけを問うので、NULL の影響を受けず正しく反結合になります。

反結合を書くときはNOT EXISTSを使う、またはNOT INを使うなら内側でWHERE 列 IS NOT NULLを付けて NULL を除く、のどちらかにします。

本講座ではNOT EXISTSを標準の書き方として使います。

-- 内側の集合に NULL が混じる例を team で作る
-- team(mgr_emp) に NULL を 1 つ入れておく
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
-- 落とし穴: NULL があると NOT IN は 0 行になる
SELECT COUNT(*) FROM perf_sales
WHERE emp_id NOT IN (SELECT mgr_emp FROM team);

-- 回避: NOT EXISTS なら NULL に影響されず正しい反結合
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
SELECT COUNT(*) FROM perf_sales p
WHERE NOT EXISTS (
  SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id
);

内側の集合に NULL が混じったときのNOT INとNOT EXISTSの差を、件数で観察します。本記事の最後の演習です。teamという小さな値リスト（3, 7, NULLの 3 値）を CTE で用意し、「emp_idがそのリストに含まれないperf_salesの件数」を 2 通りで求めます。

① WITH team(mgr_emp) AS (VALUES ...)で3, 7, NULLを持つ CTE を作り、emp_id NOT IN (SELECT mgr_emp FROM team)で件数を数えてください。

② 同じ判定をNOT EXISTS (SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id)で書き、件数を数えてください。

③ 2 つの件数を見比べ、NOT IN版が 0 件になっていることを確認してください。