Day9 後半のゴール
「EXISTS と IN を“なんとなく同じ”ではなく、意図と性能で使い分けられるようにする」
前半で、EXISTS は「存在チェック」、IN は「集合に属するか」という発想の違いを押さえました。
後半では、もう一歩踏み込んで、
どんなときに EXISTS の方が有利になりやすいか
IN が苦手になりやすいパターンは何か
JOIN に書き換えた方がよいケースはどこか
を、具体的なイメージと一緒に整理していきます。
EXISTS と IN の“動き方”のイメージをもう一度
「EXISTS は“見つかったら即OK”、IN は“リストを意識しがち”」
同じ「注文があるユーザー」を取る例で、動き方のイメージをもう一度整理します。
IN 版は、頭の中ではこう動いているイメージです。
orders から user_id の集合を作る
その集合の中に users.id が含まれているかをチェックする
EXISTS 版は、こうです。
users の1行ごとに、orders に user_id = u.id の行が1件でもあるかを聞く
見つかった時点で「このユーザーは条件を満たす」と判断できる
特に重要なのは、EXISTS は「1件見つかったらそれで十分」という性質です。
サブクエリの中で何件ヒットしようが、1件目が見つかった時点で「存在する」と判断できます。
MySQL はこの性質を利用して、
EXISTS のサブクエリを「最初の1件を見つけるための検索」として最適化しやすくなります。
大量データのときに差が出やすいパターン
「サブクエリ側が“でかい集合”になる IN はつらくなりやすい」
次のようなケースを想像してみてください。
users は10万件
orders は1000万件
やりたいことは同じく「注文があるユーザー」を取ることです。
IN 版では、サブクエリがこうなります。
SELECT o.user_id
FROM orders o;
もし何も工夫しなければ、
orders の全行から user_id を集めた「巨大な集合」を扱うことになります。
もちろん、実際のMySQLは内部で最適化しますが、
概念的には「でかいリストを相手にしている」イメージです。
一方、EXISTS 版では、
SELECT 1
FROM orders o
WHERE o.user_id = u.id;
users の1行ごとに、
「orders に user_id = u.id の行が1件でもあるか」を調べます。
orders.user_id にインデックスがあれば、
特定の user_id に対する検索は非常に速くなります。
ここでの直感はこうです。
サブクエリ側が巨大で、かつ「存在するかだけ知りたい」なら、EXISTS の方がイメージ的に自然で、MySQLも最適化しやすいことが多い。
IN が苦手になりやすいパターン
「サブクエリが複雑で、かつ大量の値を返すとき」
IN 自体が悪いわけではありませんが、
次のような条件が重なると、IN は苦しくなりやすいです。
サブクエリが大量の行を返す
サブクエリの中でさらにJOINや集計をしている
左側のカラムに適切なインデックスがない
例えば、こういう書き方です。
SELECT
u.id,
u.name
FROM users u
WHERE u.id IN (
SELECT o.user_id
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE i.product_id = 123
);
このサブクエリは、
特定の商品を含む注文の user_id を全部集める
という意味ですが、
対象期間が長かったり、人気商品だったりすると、
かなりの件数の user_id が返ってくる可能性があります。
このような「でかい集合」を扱うとき、
EXISTS で書き直した方が、
MySQLにとっても人間にとっても分かりやすくなることが多いです。
EXISTS に書き直した例
「“ユーザーごとに、その商品を含む注文があるか”と考える」
さっきのクエリを EXISTS で書き直してみます。
SELECT
u.id,
u.name
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = u.id
AND i.product_id = 123
);
この書き方だと、読み手の頭の中ではこう動きます。
ユーザーごとに
「このユーザーの注文の中に、product_id=123 を含むものが1件でもあるか？」をチェックする
EXISTS の性質上、
1件見つかった時点で「このユーザーは条件を満たす」と判断できます。
orders.user_id や order_items.order_id にインデックスがあれば、
MySQLはそれを使って「最初の1件」を素早く見つけに行けます。
ここでのポイントは、
EXISTS に書き直すことで、
「ユーザーごとの存在チェック」という意図が明確になる
MySQLにとっても「最初の1件を探す検索」として最適化しやすくなる
という二重のメリットがあることです。
NULL が絡むときの IN の落とし穴
「IN は NULL を“含まない”という仕様」
もう1つ、IN を使うときに気をつけたいのが NULL の扱いです。
例えば、こういうテーブルを想像します。
SELECT user_id
FROM some_table;
この中に、NULL が混ざっているとします。
IN は、NULL を「集合の要素」として扱いません。
つまり、
WHERE u.id IN (NULL, 1, 2, 3)
のような条件は、
「u.id が1,2,3のどれか」という意味であり、
NULL は無視されます。
一方、EXISTS は「行が存在するかどうか」だけを見るので、
NULL が混ざっていても関係ありません。
実務では、
サブクエリ側のカラムにNULLが入りうるとき、
IN で書くと意図しない除外が起きることがあります。
Day9 の段階では、
IN は NULL を含まない
EXISTS は「行の有無」なのでNULLの有無に影響されない
という違いだけ、頭の片隅に置いておいてください。
JOIN に書き直した方がよいケース
「“結果としてJOINしたい”なら、最初からJOINで書く」
EXISTS / IN / JOIN は、
内部的にMySQLが書き換えることもありますが、
人間が読むときの意図はかなり違います。
例えば、
「注文があるユーザーだけを取りたい」
かつ
「そのユーザーの注文の合計金額も一緒に見たい」
というケース。
EXISTS で「注文があるユーザー」を絞り込んだあと、
別のクエリで合計金額を取りに行く、という書き方もできますが、
素直にJOIN＋GROUP BYで書いた方が読みやすいことが多いです。
SELECT
u.id,
u.name,
SUM(o.total) AS total_amount
FROM users u
JOIN orders o
ON o.user_id = u.id
GROUP BY u.id, u.name;
この場合、
「注文があるユーザーだけ」
という条件は、JOINの時点で自然に満たされます。
EXISTS は「存在チェック専用」
JOIN は「実際に結合してデータを持ってきたいとき」
という役割分担で考えると、
どこで何を使うかが整理しやすくなります。
セキュリティ・運用の視点から見た EXISTS の良さ
「“怪しい痕跡が1件でもあるか”を素直に書ける」
EXISTS は、「1件でもあればいい」という条件を
とても素直に書ける構文です。
例えば、
不正IPからのアクセスが1件でもあるユーザー
特定の危険な操作ログが1件でもあるセッション
特定のエラーコードを1回でも出したサーバー
こういった「痕跡が1回でもあれば要注意」という条件は、
セキュリティや監査の文脈でよく出てきます。
EXISTS を使うと、
WHERE EXISTS (
SELECT 1
FROM logs l
WHERE l.user_id = u.id
AND l.action = 'SUSPICIOUS'
);
のように、「存在チェックしている」ことが一目で分かります。
これは、
後からSQLを読む人にとっても、
「何を検知しようとしているのか」が伝わりやすく、
監査・レビューの質を上げることにもつながります。
Day9 後半のまとめ
IN は「サブクエリが返す値の集合に左側の値が含まれているか」を見る構文で、サブクエリが大量の値を返す場合や、内部でさらにJOIN・集計をしている場合には、「でかい集合」を扱うイメージになりやすく、インデックスや最適化の状況によっては負荷が高くなりがちである。
EXISTS は「サブクエリが1行でも返すかどうか（存在するかどうか）」だけを判定する構文で、1件見つかった時点で「存在する」と判断できるため、WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND ...) のように書くと、「ユーザーごとに対応する行が1件でもあるか」を効率よくチェックしやすく、特にサブクエリ側が大きいときや“存在チェックだけしたい”ときに有利になりやすい。
IN は NULL を集合の要素として扱わない（IN (NULL, 1, 2) は実質 IN (1, 2)）一方で、EXISTS は「行の有無」だけを見るためNULLの有無に影響されず、サブクエリ側にNULLが混ざる可能性があるときには、IN で意図しない除外が起きないかを意識する必要がある。
実務では、「実際に結合してデータを持ってきたい」場合はJOIN、「その条件を満たす行が1件でも存在するかだけ知りたい」場合はEXISTS、「単純にIDの集合に属するかを見たい」場合はIN、という役割分担で考えると整理しやすく、特にセキュリティや監査の文脈では、EXISTS を使うことで“怪しい痕跡が1件でもあるか”という意図を素直に表現できるようになる。