IN / EXISTS

IN: 値がサブクエリの結果に含まれるかを判定します。

SELECT column FROM table
WHERE column IN (SELECT column FROM subquery);

NOT IN: 値がサブクエリの結果に含まれない行を取得します。

SELECT column FROM table
WHERE column NOT IN (SELECT column FROM subquery);

EXISTS: サブクエリが1行以上返す場合に真になります。

SELECT column FROM table AS a
WHERE EXISTS (
    SELECT 1 FROM subquery WHERE subquery.key = a.key
);

NOT EXISTS: サブクエリが0行の場合に真になります。

SELECT column FROM table AS a
WHERE NOT EXISTS (
    SELECT 1 FROM subquery WHERE subquery.key = a.key
);

構文	概要
IN (SELECT ...)	値がサブクエリの結果に含まれる行を返します。
NOT IN (SELECT ...)	値がサブクエリの結果に含まれない行を返します。NULLが混在すると意図しない結果になります。
EXISTS (SELECT ...)	サブクエリが1行以上返す場合に真となります。
NOT EXISTS (SELECT ...)	サブクエリが0行の場合に真となります。

以下の『customers』テーブルと『orders』テーブルを例に説明します。

注文を持つ顧客を IN で取得します。

sample_in_exists.sql

SELECT name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

+----------+
| name     |
+----------+
| 両面宿儺 |
| 五条悟   |
| 虎杖悠仁 |
+----------+
3 rows in set

一度も注文していない顧客を NOT IN で取得します。

sample_in_exists.sql

SELECT name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

+------------+
| name       |
+------------+
| 伏黒恵     |
| 釘崎野薔薇 |
+------------+
2 rows in set

EXISTS で注文がある顧客を取得します（大量データで高速）。『SELECT 1』の『1』に特別な意味はなく、行が存在するかどうかだけを確認するための書き方です。

sample_in_exists.sql

SELECT c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);

+----------+
| name     |
+----------+
| 両面宿儺 |
| 五条悟   |
| 虎杖悠仁 |
+----------+
3 rows in set

NOT EXISTS で一度も注文していない顧客を取得します。

sample_in_exists.sql

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);

+------------+
| name       |
+------------+
| 伏黒恵     |
| 釘崎野薔薇 |
+------------+
2 rows in set

『IN』『NOT IN』『EXISTS』『NOT EXISTS』の構文は主要なデータベースで共通して使用できます。

SELECT c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);

ただし、テーブルの別名（エイリアス）に『AS』を使えるかはデータベースによって異なります。Oracle では『FROM テーブル名 別名』のように『AS』を省略して記述します。

SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

『IN』と『EXISTS』は同じ結果を返すことが多いですが、パフォーマンス特性が異なります。サブクエリの結果が大量行になる場合は『EXISTS』の方が高速なことが多いです。『EXISTS』はサブクエリが1行でも見つかった時点で評価を打ち切るためです。

『NOT IN』はサブクエリの結果に NULL が1件でも含まれると、すべての行が FALSE と評価されて0件になります。これは NULL との比較が UNKNOWN を返す SQL の仕様によるものです。NULL が混在する可能性がある場合は『NOT EXISTS』を使うか、サブクエリに『WHERE 列 IS NOT NULL』を追加してください。

サブクエリで単一値を返す比較は『サブクエリ（スカラー・行）』を、全値・任意値との比較は『ALL / ANY』を参照してください。