IN・NOT INで候補を含める・除外したい
INは候補に一致する行、NOT INは候補から外れる行を探します。除外リストや比較する列にNULLがある場合は、結果が変わる点に注意します。
SQL例の例
SELECT name, department_id
FROM employees
WHERE department_id IN (1, 3)
ORDER BY id;この例で使うサンプル表を見る共通の架空データと、練習用の表を確認します
全20項目で共通の架空データです。salary は月給（円）。NULL は未設定を表します。結果の小数点以下の表示桁数は実行環境により異なります。
employees
| id | name | department_id | salary |
|---|---|---|---|
| 1 | 佐藤 | 1 | 300000 |
| 2 | 鈴木 | 2 | 400000 |
| 3 | 佐々木 | 1 | 350000 |
| 4 | 高橋 | 2 | 400000 |
| 5 | 田中 | NULL | 250000 |
| 6 | 伊藤 | 3 | NULL |
departments
| id | name |
|---|---|
| 1 | 営業 |
| 2 | 開発 |
| 3 | 人事 |
| 4 | 広報 |
employees.department_id が departments.id に対応します。表は説明用です。このサイト上でSQLは実行しません。
使い方を順番に確認する練習を始めるときは、この手順に沿って進めます
使い方
- 候補となる部署IDを調べる
- IN (1, 3) と書く
- 該当社員の名前と部署IDを確認する
結果の例
| name | department_id |
|---|---|
| 佐藤 | 1 |
| 佐々木 | 1 |
| 伊藤 | 3 |
NULLは不明な値です。WHEREは条件がTRUE（真）の行だけを残し、FALSE（偽）とUNKNOWN（不明）は残しません。NOT INを付ければIN以外の全行になる、とは限りません。
NOT INとNULL：0件になる例と直し方
1. 営業部以外を探す：未設定の部署は含まれない
SELECT e.name, e.department_id
FROM employees AS e
WHERE e.department_id NOT IN (1)
ORDER BY e.id;営業部IDは1です。部署2と3の3人が残ります。田中さんのNULLは1と違うと確定できないため、結果に含まれません。
| name | department_id |
|---|---|
| 鈴木 | 2 |
| 高橋 | 2 |
| 伊藤 | 3 |
2. 除外リストにNULLがあると0件になる
WITH excluded_departments (department_id) AS (
VALUES (1), (NULL)
)
SELECT e.name, e.department_id
FROM employees AS e
WHERE e.department_id NOT IN (
SELECT department_id FROM excluded_departments
)
ORDER BY e.id;WITHはこのSELECT文の中だけで使う仮の一覧を作ります。除外候補は1とNULLです。部署1はFALSE、部署2・3や未設定はUNKNOWNになり、TRUEの行がないため0件です。構文エラーではありません。
| name | department_id |
|---|
3. 除外リストからNULLを取り除く
WITH excluded_departments (department_id) AS (
VALUES (1), (NULL)
)
SELECT e.name, e.department_id
FROM employees AS e
WHERE e.department_id NOT IN (
SELECT department_id FROM excluded_departments
WHERE department_id IS NOT NULL
)
ORDER BY e.id;内側のSELECTでNULLを取り除くと、除外候補は1だけになります。ただし社員側の部署NULLは依然として残りません。この例では「未設定の除外候補は無視する」という業務上のルールを採用しています。
| name | department_id |
|---|---|
| 鈴木 | 2 |
| 高橋 | 2 |
| 伊藤 | 3 |
4. NOT EXISTS：一致する除外候補がない社員を残す
WITH excluded_departments (department_id) AS (
VALUES (1), (NULL)
)
SELECT e.name, e.department_id
FROM employees AS e
WHERE NOT EXISTS (
SELECT 1 FROM excluded_departments AS x
WHERE x.department_id = e.department_id
)
ORDER BY e.id;NOT EXISTSは内側のSELECTに一致する行がないかを調べます。SELECT 1の1は人数ではなく、行の存在を調べるための定数です。NULL同士も = では一致しないので、部署未設定の田中さんも残ります。上のNOT INとは結果が異なります。
| name | department_id |
|---|---|
| 鈴木 | 2 |
| 高橋 | 2 |
| 田中 | NULL |
| 伊藤 | 3 |
5. NOT EXISTSでも部署未設定の社員は除きたい
WITH excluded_departments (department_id) AS (
VALUES (1), (NULL)
)
SELECT e.name, e.department_id
FROM employees AS e
WHERE e.department_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM excluded_departments AS x
WHERE x.department_id = e.department_id
)
ORDER BY e.id;社員側にもIS NOT NULLを付け、部署未設定は対象外というルールを明示します。どちらが正しいかは目的次第です。NOT EXISTSが常に高速、常にNOT INと同じ結果という意味ではありません。
| name | department_id |
|---|---|
| 鈴木 | 2 |
| 高橋 | 2 |
| 伊藤 | 3 |
うまくいかないときに確認するエラーや想定と違う結果が出たときの確認ポイント
うまくいかないとき
NOT INで0件になったら、除外候補を返す内側のSELECTを単独で確認し、NULLが含まれていないか調べます。次に社員側のNULLを残すか決めます。ここでの例は共通サンプル表とSELECT内の仮データだけを使用し、実データを更新しません。
PostgreSQLの構文を基準にしています。MySQL・SQL Server・SQLiteなどでは、一部の構文や挙動が異なります。