相関EXISTSとNOT EXISTS
NULLを正しく扱える、堅牢なアンチJOINの代替方法を学びます。
「相関EXISTSとNOT EXISTS」はCoddyKit上の無料Coding Interview Prepレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはCoding Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Coding Interview Prepコースには全4レッスンが含まれています。
EXISTSは存在をテストする
EXISTS はサブクエリを受け取り、そのサブクエリが少なくとも1行を返した時点で TRUE を返し、それ以外では FALSE を返します。行そのものを返すことはありません。
相関サブクエリを内側に置くと、EXISTS は外側の行ごとの存在チェックになります。つまり、「この外側の行に一致する行は存在するか」を確認します。
最初に一致した時点で処理を短絡できるため、一致する行が何行あるかは関係ありません。この意味上の細部は、面接でよく問われます。
基本的な相関EXISTS
少なくとも1件の注文を行った顧客を見つけます。内側のクエリは o.customer_id = c.customer_id を通じて相関しています。
顧客ごとに、EXISTS は「この顧客の注文が1件でも存在するか」と確認します。存在すれば、その顧客を残します。
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);EXISTS内でSELECT 1を使う理由
EXISTS の中では、SELECT 1、SELECT *、SELECT NULL のいずれも使われているのを見かけます。これらはすべて同じ意味です。
EXISTS が確認するのは行が返るかどうかだけで、行の内容ではありません。そのため、射影する列は無関係です。オプティマイザもそれらを無視します。
SELECT 1 は、意図を示す一般的な慣例です。「存在するかどうかだけを知りたい」という意味になります。どれか1つを選んで一貫して使い、ここでは列リストが重要だと面接官に誤解させないようにしてください。
NOT EXISTSで存在しないものを見つける
NOT EXISTS は判定を反転させます。相関サブクエリが1行も返さない場合にだけ、外側の行を残します。
これは典型的なアンチ結合です。注文のない顧客、販売されたことのない商品、提出物のない学生などを見つけるために使います。
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);NOT INとNULLの落とし穴
ここは面接で特に重要なポイントです。NULL を含む可能性のあるサブクエリに対して NOT IN を使うと、危険な動作になります。リストに NULL が1つでも含まれていると、NOT IN は一切行を返しません。
NULL との比較結果は UNKNOWN になり、NOT IN ではすべての比較結果が偽である必要があるためです。UNKNOWN が1つあるだけで、条件全体が成立しなくなります。
NOT EXISTS にはこの問題がありません。単純に行の存在を確認するため、NULL があっても安全に処理できます。
-- Risky: returns nothing if any o.customer_id is NULL
SELECT c.customer_id FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);
-- Safe: NULLs do not break it
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);NOT EXISTSがNULLに安全な理由
理由は一致判定の仕組みにあります。NOT EXISTS は、o.customer_id = c.customer_id を満たす内側の行が存在するかどうかを確認します。
o.customer_id が NULL の行は、その等価条件を満たしません（NULL = 何らかの値の結果は TRUE ではなく UNKNOWN です）。そのため、単に一致として数えられません。存在チェックは正しく保たれます。
NOT IN では、同じ NULL がリスト比較の一部となり、UNKNOWN の結果によってすべての出力が消えてしまいます。これが、上級者向けの面接で NOT EXISTS が好まれる理由です。
追加条件付きのEXISTS
相関サブクエリには、さらに条件を追加できます。1000を超える注文を少なくとも1件行った顧客を検索します。
追加条件はEXISTSサブクエリの内部に記述し、顧客ごとの範囲で適用します。
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.amount > 1000
);パフォーマンス：短絡評価の動作
EXISTSは、一致する行が1件見つかるとすぐに内部リレーションの走査を停止できます。結果セット全体を作成したり、件数を数えたりすることはありません。
そのため、EXISTSは一般的に効率的です。特に相関に使う列にインデックスがある場合は、行ごとの検索で一致をすばやく見つけて処理を打ち切れるため、効果的です。
相関サブクエリのCOUNT(*) > 0と比較してみましょう。こちらは一致する行をすべて数える必要があります。必要なのがyes/noの判定だけであれば、EXISTSを優先してください。
存在確認におけるEXISTSとCOUNTの比較
候補者は、存在を確認するために相関COUNTを書くことがあります。動作はしますが、余計な処理が発生します。
COUNT版は一致する注文をすべて数えますが、EXISTSは最初の1件で終了します。単純な存在確認では、EXISTSのほうが意図を明確に伝え、オプティマイザーによる短絡評価も可能にします。
-- Works but counts everything
SELECT c.customer_id FROM customers c
WHERE (SELECT COUNT(*) FROM orders o
WHERE o.customer_id = c.customer_id) > 0;
-- Better: stops at first match
SELECT c.customer_id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);実践例：一度も注文されていない商品
面接でよく出るアンチ結合の問題として、一度も注文されていない商品を一覧にするものがあります。NOT EXISTSを使うと、英語の要件に近い形で読めます。
商品ごとに、その商品を参照する注文明細が存在するかを確認し、存在しない商品だけを残します。
SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM order_items oi
WHERE oi.product_id = p.product_id
);除算型クエリにおけるNOT EXISTS内のEXISTS
NOT EXISTSの内側にEXISTSをネストすると、関係代数の除算を表現できます。つまり、集合内のすべてに一致する行を検索するということです。典型的な問題は、カテゴリ内のすべての商品を注文した顧客を検索するものです。
ロジックは、注文していない商品が存在しない顧客を残す、というものです。この二重否定は除算クエリの特徴であり、面接官は候補者のEXISTSへの深い理解を確認するためにこの問題を使います。
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM products p
WHERE p.category = 'Coffee'
AND NOT EXISTS (
SELECT 1 FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE oi.product_id = p.product_id
AND o.customer_id = c.customer_id
)
);確認問題
注文がない顧客を検索する最も安全な方法を選んでください。
まとめ：相関EXISTSとNOT EXISTS
重要なポイント：
EXISTSは行ごとの存在確認であり、最初の一致で短絡評価されます。内部で選ぶ列は結果に影響しないため、SELECT 1を使います。NOT EXISTSはNULLの影響を受けないアンチ結合であり、一致する行がない行を検索できます。- リスト内にNULLがある
NOT INは何も返しません。NOT EXISTSを優先してください。 - 存在確認では、相関
COUNT(*) > 0よりEXISTSが優れています。最初の一致で処理を停止できるためです。
こちらから言わなくてもNOT INのNULLの落とし穴に触れられると、SQLへの習熟度を確実に示せます。
よくある質問
「相関EXISTSとNOT EXISTS」レッスンは無料ですか？
はい。「相関EXISTSとNOT EXISTS」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。
「相関EXISTSとNOT EXISTS」で何を学びますか？
NULLを正しく扱える、堅牢なアンチJOINの代替方法を学びます。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Coding Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「相関EXISTSとNOT EXISTS」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このCoding Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 相関サブクエリの構造
- GROUP BYなしでグループごとに集計する
- 相関EXISTSとNOT EXISTS
- 相関サブクエリをJOINに書き換える