Day22 後半
「EXISTS / IN を“なんとなく”から“意図して選ぶ”レベルに上げる」
前半で、EXISTS / IN が
「別テーブルに“いるかどうか”を聞く道具」
というところまでは掴めました。
後半では一歩進んで、
EXISTS と IN の違いをもう少し丁寧に整理する
NOT EXISTS / NOT IN の落とし穴（特に NULL）
JOIN で書く場合との比較
セキュリティ・設計の観点でどう使い分けるか
を、具体例ベースで固めていきます。
ここを越えると、
「とりあえず IN」ではなく、
「ここは EXISTS、一方こっちは IN」と選べるようになります。
EXISTS と IN を同じ問題で並べて比べる
「注文を持つユーザー」クエリをもう一度
前半と同じテーブルを使います。
users
id | name
---+-----------
1 | 山田太郎
2 | 佐藤花子
3 | 鈴木一郎
orders
id | user_id | amount
---+---------+-------
1 | 1 | 1200
2 | 1 | 3000
3 | 2 | 500
「注文を1件以上持っているユーザーだけ」を取りたいとき。
EXISTS 版はこうでした。
SELECT
u.id,
u.name
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);
IN 版はこうでした。
SELECT
u.id,
u.name
FROM users u
WHERE u.id IN (
SELECT o.user_id
FROM orders o
);
どちらも結果は同じですが、
頭の中のイメージは違います。
EXISTS は「このユーザーに対応する行が orders に1件でもある？」
IN は「このユーザーの id は、“orders に出てくる user_id の集合”に含まれている？」
この違いを意識しておくと、
後で NOT EXISTS / NOT IN を理解するときに効いてきます。
NOT EXISTS と NOT IN
「いない人を探す」ときに NULL が牙をむく
次の要件を考えます。
「一度も注文していないユーザーだけを取りたい」
欲しい結果はこうです。
id | name
---+-----------
3 | 鈴木一郎
まずは NOT EXISTS で書いてみます。
SELECT
u.id,
u.name
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);
これは素直に読めます。
「このユーザーに対応する行が orders に1件も存在しないなら残す」
→ 注文ゼロのユーザーだけが残る。
一方、NOT IN で書くとこうなります。
SELECT
u.id,
u.name
FROM users u
WHERE u.id NOT IN (
SELECT o.user_id
FROM orders o
);
一見、同じ意味に見えますが、
ここで重要なのが NULL の存在 です。
もし orders.user_id に NULL が混ざっていた場合、NOT IN (…NULL を含む集合…) は、
「比較結果が UNKNOWN になってしまい、全員落ちる」
というややこしい挙動になります。
SQLite でも、x NOT IN (1, NULL) のような式は
TRUE / FALSE ではなく UNKNOWN になり、
WHERE では「条件を満たさない」と扱われます。
結果として、
「誰も返ってこない NOT IN」
という、かなり危険な状態になります。
NOT EXISTS は「行があるかどうか」だけを見ているので、
NULL の影響を受けません。
このため、
「いない人を探す」系のクエリでは、
NOT IN より NOT EXISTS を選ぶのが安全です。
EXISTS / IN と JOIN の比較
「結果として行を“増やす”か、“フィルタする”か」
同じ「注文を持つユーザー」を JOIN で書くとこうなります。
SELECT DISTINCT
u.id,
u.name
FROM users u
JOIN orders o
ON u.id = o.user_id;
JOIN は「行をくっつける」操作なので、
ユーザー1人に対して注文が2件あれば、
そのユーザーの行は2行に増えます。
DISTINCT を付ければ重複は消せますが、
「一度 JOIN してから重複を消す」という余計なステップが入ります。
一方、EXISTS / IN は
「users の行をフィルタする」だけです。
EXISTS
→ 「この行を残すかどうか」を、サブクエリの存在で決める
IN
→ 「この id を残すかどうか」を、値の集合で決める
どちらも、users 側の行数は変えずに
「残すか・捨てるか」だけを決めます。
この違いは、
「結果として行を増やしたいのか、
それともフィルタしたいだけなのか」
という観点で使い分けると分かりやすいです。
パフォーマンスのざっくりした感覚
「EXISTS は“見つかったらすぐやめる”、IN は“集合を作る”」
実際の最適化はエンジン次第ですが、
ざっくりしたイメージを持っておくと役に立ちます。
EXISTS の場合、
サブクエリは「1行でも見つかったら TRUE」でよいので、
条件に合う行を見つけた時点で探索を打ち切れます。
IN の場合、
サブクエリ側の結果を「値の集合」として扱うので、
一度その集合を作ってから
「含まれているか」をチェックするイメージになります。
SQLite は賢いので、
単純に「EXISTS が常に速い」「IN が常に遅い」とは言えませんが、
「行の存在を聞きたいときは EXISTS」
「値の集合に含まれるかを聞きたいときは IN」
という本来の役割に沿って使っておくと、
最適化とも相性が良くなりやすいです。
セキュリティと設計の視点から見る EXISTS / IN
「“存在だけ聞く”クエリは、情報を最小限にできる」
セキュリティの観点で見ると、
EXISTS / IN の良さは
「必要な判定だけを行い、余計な情報を引き出さなくて済む」
という点にあります。
例えば、
「このユーザーが管理者ロールを持っているか？」
を知りたいだけなら、
SELECT EXISTS (
SELECT 1
FROM user_roles
WHERE user_id = ? AND role = 'admin'
);
のように、
EXISTS で TRUE / FALSE だけ返せば十分です。
わざわざロールの一覧を全部返して
アプリ側で判定する必要はありません。
これは、
データベースから外に出る情報量を減らす
→ 情報漏えいのリスクを下げる
という意味で、とても健全な設計です。
また、NOT EXISTS を使って
「どのロールにも属していないユーザー」
「どのプロジェクトにも紐づいていない API キー」
などを定期的に洗い出すクエリを用意しておくと、
“放置された危険なアカウント” を見つけるのにも役立ちます。
小さな練習イメージ
頭の中で、次の3つのクエリを考えてみてください。
注文を1件以上持つユーザーを取りたい
→ EXISTS / IN / JOIN の3パターンで書いてみる
一度も注文していないユーザーを取りたい
→ NOT EXISTS と NOT IN で書いてみて、NULL があったらどうなるか想像する
特定のロールを持つユーザーが“存在するかどうか”だけ知りたい
→ SELECT EXISTS (…) で TRUE / FALSE だけ返す形を考える
このあたりを自分の手で書いてみると、
EXISTS / IN / JOIN の使い分けが
かなりクリアになってきます。
Day22 後半のまとめ
EXISTS は「行の存在」を聞く道具、IN は「値の集合に含まれるか」を聞く道具で、同じ問題でも頭の中のイメージが違う。
NOT EXISTS は「いない人」を探すときに素直に使えるが、NOT IN はサブクエリ側に NULL が混ざると全員落ちるなど挙動が危険になりやすい。
JOIN は「行を増やして結合する」操作であり、EXISTS / IN は「元の行をフィルタする」操作なので、結果の行数がどう変わるかを意識して選ぶとよい。
パフォーマンスの感覚としては、「行の存在チェックなら EXISTS」「値の集合チェックなら IN」という本来の役割に沿って使うと、エンジンの最適化とも相性が良くなりやすい。
EXISTS を使って「存在するかどうかだけ」を DB に聞くスタイルは、余計な情報を外に出さずに済むため、権限判定やアカウント棚卸しなどセキュリティ面でも有効なパターンになる。
ここまで来たあなたは、
EXISTS / IN を「知っている構文」ではなく、
「設計の意図に合わせて選べる道具」として扱える段階に入っています。
この感覚があると、WHERE 句の表現力と安全性が一気に上がります。