IN、ANY、ALLのサブクエリ
集合のメンバーシップを調べるサブクエリと、NULLを含むNOT INの有名な落とし穴を学びます。
「IN、ANY、ALLのサブクエリ」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
集合所属判定のサブクエリ
サブクエリが値のリストを返す場合は、IN、ANY、ALLを使ってその集合への所属を判定します。これらは、SQLでこの値はその集合に含まれているか、またはその集合の要素のいずれか／すべてを上回っているかを尋ねる方法です。
IN— リスト内のいずれかの値に一致します。ANY/SOME— 比較が少なくとも1つの要素に対して成立すればtrueになります。ALL— すべての要素に対して成立する場合にのみtrueになります。
サブクエリでの IN
日常的によくある例は、'NYC'にあるいずれかの部署で働く従業員を探すことです。サブクエリは部署IDの集合を返し、INはそのいずれかに一致する行を残します。
自然に読める書き方で、面接官がまず期待する形式でもあります。
SELECT name
FROM employees
WHERE dept_id IN (
SELECT id FROM departments WHERE city = 'NYC'
);= ANY は IN と同じ
面接官が好む分かりやすい等価関係があります。= ANY (subquery)は、IN (subquery)とまったく同じ意味です。どちらも、値が集合の少なくとも1つの要素と等しい場合に成立します。
以下のクエリは、前のクエリと同じ結果を返します。ANYとその同義語であるSOMEを使うと、>や<など、ほかの演算子にも一般化できます。
SELECT name
FROM employees
WHERE dept_id = ANY (
SELECT id FROM departments WHERE city = 'NYC'
);比較演算子と ANY
ANYは、>や<と組み合わせると強力です。salary > ANY (set)は、salaryが少なくとも最小の要素より大きい場合にtrueになります — つまり、最小値より大きいということです。
これにより、部署5の少なくとも1人より多く稼いでいる従業員を見つけられます。
SELECT name, salary
FROM employees
WHERE salary > ANY (
SELECT salary FROM employees WHERE dept_id = 5
);比較演算子と ALL
salary > ALL (set)は、salaryがすべての要素を上回る場合にのみtrueになります — つまり、最大値より大きいということです。これにより、部署5の全員より多く稼いでいる従業員を見つけられます。
次のショートカットを覚えておきましょう: > ALL = MAXより大きい、> ANY = MINより大きい。面接では非常によく問われます。
SELECT name, salary
FROM employees
WHERE salary > ALL (
SELECT salary FROM employees WHERE dept_id = 5
);NOT IN: 有名な NULL の落とし穴
これは、サブクエリの面接問題で最も頻繁に聞かれる落とし穴です。NOT IN内のサブクエリが1つでもNULLを返すと、NOT IN全体が行をまったく返さないことがあります — 期待した孤立した行が返るとは限りません。
なぜでしょうか。x NOT IN (1, 2, NULL)は、x <> 1 AND x <> 2 AND x <> NULLに展開されます。最後の比較はUNKNOWNになるため、AND全体がtrueになることはありません。
SELECT name
FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders
);そのクエリがひそかに壊れる理由
orders.customer_idがNULLを許容し、そこにNULLが入っている行が1つでもあると、注文のない顧客が明らかに存在していても、前のクエリは0行を返します。
- NULLが存在すると、ロジック全体が
UNKNOWNになります。 - エラーにはなりません — ただ誤った（空の）結果を返します。
面接中にこの危険性をはっきり説明できると、強いアピールになります。
NULL を含む NOT IN の修正
面接官が認める安全な修正方法は3つあります:
- サブクエリでNULLを除外します:
WHERE customer_id IS NOT NULLを追加します。 NOT EXISTSを使って書き換えます。NULLを正しく処理できます。LEFT JOIN ... IS NULLのアンチJOINを使用します。
以下のNULL対策済みのバージョンは、注文のない顧客の正しい一覧を返します。
SELECT name
FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders
WHERE customer_id IS NOT NULL
);IN は NULL があっても問題ない
安心できる反例もあります。通常のIN（否定なし）は、リスト内のNULLによって壊れることはありません。x IN (1, 2, NULL)は、xが1または2と等しい場合にtrueになります。NULLは一致しないだけです。
NULLの危険性はNOT INに特有のものです。この2つのケースの違いを知っていることが、自信のある回答と推測による回答を分けます。
複数列の IN
一部の方言（Postgres、MySQL）では、列のタプルに対してINを使用し、複数の組を一度に照合できます。これにより、(product, region)の組み合わせがプロモーションテーブルに存在する注文行を見つけられます。
SQL Serverには行値のINがないため、そこで使用する場合はEXISTSに書き換えます。この移植性の違いに触れると、面接官に好印象を与えられます。
SELECT *
FROM order_lines
WHERE (product_id, region) IN (
SELECT product_id, region FROM promotions
);面接での要点
次のように答えてください: 「INは集合への所属を判定し、= ANYと同じです。比較演算子と組み合わせた場合、> ANYは最小値より大きいことを、> ALLは最大値より大きいことを意味します。最大の落とし穴は、NULLを返す可能性のあるサブクエリに対するNOT INです。これを使うと、気づかないうちに行がまったく返らなくなるため、IS NOT NULLで対策するか、NOT EXISTSに切り替えます。」
この回答なら、集合への所属、ANY/ALLの意味、NULLの落とし穴を一息で押さえられます。
簡単な確認
面接官が最も好むサブクエリの落とし穴です。
まとめ
集合所属判定のサブクエリをマスターしましょう:
IN== ANY: 集合内のいずれかの要素に一致します。> ANYは最小値より大きいことを、> ALLは最大値より大きいことを意味します。- サブクエリ内にNULLがある
NOT INは、気づかないうちに行をまったく返さなくなります —IS NOT NULLで対策するか、NOT EXISTSを使います。 - 通常の
INはNULLを許容します。複数列のINは一部の方言で使用できます。
次は、EXISTSとINの違い、およびシニア向け面接でよく問われるパフォーマンスの問題です。
よくある質問
「IN、ANY、ALLのサブクエリ」レッスンは無料ですか？
はい。「IN、ANY、ALLのサブクエリ」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「IN、ANY、ALLのサブクエリ」で何を学びますか？
集合のメンバーシップを調べるサブクエリと、NULLを含むNOT INの有名な落とし穴を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「IN、ANY、ALLのサブクエリ」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- SELECTとWHEREのスカラーサブクエリ
- FROM句のサブクエリ（派生テーブル）
- IN、ANY、ALLのサブクエリ
- EXISTSとINのパフォーマンス