【SQL】NOT IN完全ガイド｜基本構文・NULL問題の原因と対策・NOT EXISTS・LEFT JOIN比較・DELETE/UPDATE・複数列まで解説

-- employees テーブル（従業員）
-- id | name       | department | salary | manager_id
--  1 | 田中 太郎  | 営業       |  55000 |       NULL
--  2 | 鈴木 花子  | 開発       |  70000 |          1
--  3 | 高橋 一郎  | 営業       |  48000 |          1
--  4 | 伊藤 美咲  | 開発       |  80000 |       NULL
--  5 | 渡辺 健二  | 人事       |  52000 |          4
--  6 | 中村 さくら| NULL       |  65000 |          4

-- orders テーブル（注文）
-- order_id | customer_id | status
--        1 |           1 | shipped
--        2 |           3 | shipped
--        3 |           1 | pending

-- customers テーブル（顧客）
-- id | name
--  1 | 田中
--  2 | 鈴木
--  3 | 高橋
--  4 | 伊藤

-- 営業・開発以外の部門の従業員を取得
SELECT id, name, department
FROM employees
WHERE department NOT IN ('営業', '開発');

-- 結果:
-- id=5: 渡辺 健二（人事）✓
-- ※ id=6 は department が NULL なので NOT IN でも除外される（後述）

-- IN と NOT IN の対称性（両方で全件カバーされる）
-- IN を取得
SELECT name FROM employees WHERE department IN ('営業', '開発');
-- NOT IN を取得
SELECT name FROM employees WHERE department NOT IN ('営業', '開発');
-- ← NULL の行は両方の結果に含まれない！

-- 特定ステータスを除く注文を取得
SELECT order_id, customer_id, status
FROM orders
WHERE status NOT IN ('canceled', 'returned');

-- 特定 ID を除く全行
SELECT id, name FROM customers
WHERE id NOT IN (1, 3);
-- 結果: id=2（鈴木）, id=4（伊藤）

-- 文字列リスト（カンマ区切り）
SELECT id, name, department
FROM employees
WHERE department NOT IN ('営業', '開発', '人事');
-- 結果: 0件（すべての部門が IN 内）＋ department が NULL の行も除外

-- ORDER BY との組み合わせ
SELECT id, name, salary
FROM employees
WHERE department NOT IN ('開発')
ORDER BY salary DESC;

-- 注文したことがない顧客を取得（customers にいて orders にいない）
SELECT id, name
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders
);
-- 結果: id=2（鈴木）, id=4（伊藤）✓

-- shipped の注文がない顧客
SELECT id, name
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE status = 'shipped'
);

-- 管理職（manager_id が参照している id）以外の従業員
SELECT id, name
FROM employees
WHERE id NOT IN (
    SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
    -- ↑ IS NOT NULL が重要！なければ全件 0 件になる
);

-- manager_id には NULL が含まれる（田中・伊藤は manager_id が NULL）
-- NG: IS NOT NULL を付けないと全件 0 件
SELECT id, name
FROM employees
WHERE id NOT IN (
    SELECT manager_id FROM employees   -- manager_id に NULL が含まれる
);
-- → 0件！（期待: NULL でない manager_id に含まれない人）

-- 確認: サブクエリが返す値（NULL を含む）
SELECT manager_id FROM employees;
-- NULL, 1, 1, NULL, 4, 4
-- ↑ NULL が 2 件含まれるため NOT IN 全体が壊れる

-- OK: IS NOT NULL でサブクエリの NULL を除外する
SELECT id, name
FROM employees
WHERE id NOT IN (
    SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
);
-- 結果: id=2（鈴木）, id=3（高橋）, id=5（渡辺）, id=6（中村）✓

-- 【対処法1】サブクエリに IS NOT NULL を付ける（最もシンプル）
SELECT id, name FROM employees
WHERE id NOT IN (
    SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
);

-- 【対処法2】COALESCE で NULL を意味のない値に置換する
SELECT id, name FROM employees
WHERE id NOT IN (
    SELECT COALESCE(manager_id, -1) FROM employees
    -- manager_id が NULL の場合は -1 に変換（-1 は employees.id に存在しない値）
);

-- 【対処法3】NOT EXISTS に書き換える（NULL に強い・推奨）
SELECT e.id, e.name
FROM employees AS e
WHERE NOT EXISTS (
    SELECT 1 FROM employees AS m
    WHERE m.manager_id = e.id
    -- ↑ NULL の行は WHERE m.manager_id = e.id が UNKNOWN → EXISTS が false → NOT EXISTS が true
    -- → NULL の行は「存在しない」と判定され、正しく動作する
);

-- 対処法3 が最も安全（NULL が含まれていても正しく動作する）

-- 注文していない顧客を取得する（2つの書き方）

-- NOT IN（サブクエリの customer_id に NULL がない前提）
SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- NOT EXISTS（NULL があっても安全）
SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);

-- NOT IN と NOT EXISTS が異なる結果になるケース
-- orders.customer_id に NULL がある場合:
-- NOT IN  → 全件 0 件（NULL のせい）
-- NOT EXISTS → 正しく「注文がない顧客」を返す

-- 手法1: NOT IN
SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

-- 手法2: NOT EXISTS（相関サブクエリ）
SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);

-- 手法3: LEFT JOIN + IS NULL（アンチジョイン）
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

-- 注文のない顧客を削除（実行前に SELECT で確認必須）
-- 確認
SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

-- 削除実行
DELETE FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- 注意: MySQL では DELETE + サブクエリで同じテーブルを参照できない
-- NG (MySQL): DELETE FROM orders WHERE id NOT IN (SELECT id FROM orders WHERE status = 'shipped')
-- OK (MySQL): サブクエリを一段ネストして回避
DELETE FROM orders
WHERE id NOT IN (
    SELECT id FROM (
        SELECT id FROM orders WHERE status = 'shipped'
    ) AS tmp
);

-- PostgreSQL / SQL Server / Oracle では直接参照可能
DELETE FROM orders
WHERE id NOT IN (SELECT id FROM orders WHERE status = 'shipped');

-- 注文のない顧客のステータスを「未注文」に更新
UPDATE customers
SET status = 'inactive'
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- 特定部門以外の従業員の給与を 5% 増加
UPDATE employees
SET salary = salary * 1.05
WHERE department NOT IN ('開発', '人事');

-- UPDATE でも NULL が含まれると全件更新されない点に注意
-- IS NOT NULL フィルタを忘れずに

-- 行値コンストラクタ: (department, salary) の組み合わせで除外
-- （MySQL / PostgreSQL / Oracle 対応、SQL Server は非対応）
SELECT id, name, department, salary
FROM employees
WHERE (department, salary) NOT IN (
    ('営業', 55000),
    ('開発', 70000)
);

-- SQL Server 互換: AND で組み合わせる（すべての RDBMS で動作）
SELECT id, name, department, salary
FROM employees
WHERE NOT (department = '営業' AND salary = 55000)
  AND NOT (department = '開発' AND salary = 70000);

-- サブクエリで複数列の NOT IN
-- ordered テーブルにある (customer_id, product_id) の組み合わせ以外を取得
SELECT c.id, c.name
FROM customers c
WHERE (c.id, c.default_product_id) NOT IN (
    SELECT customer_id, product_id FROM orders WHERE status = 'shipped'
);

-- 全商品リストのうち、今月注文のない商品
SELECT p.id, p.product_name
FROM products AS p
WHERE p.id NOT IN (
    SELECT DISTINCT product_id
    FROM orders
    WHERE order_date >= '2024-04-01'
      AND order_date <  '2024-05-01'
      AND product_id IS NOT NULL  -- NULL 対策を忘れずに
);

-- 全ユーザーのうち、過去 90 日間ログインしていないユーザー
SELECT id, username
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM login_logs
    WHERE login_at >= CURRENT_DATE - INTERVAL 90 DAY
      AND user_id IS NOT NULL
);

-- 処理済みリストに含まれないレコードを処理対象に
SELECT id, data
FROM pending_tasks
WHERE id NOT IN (
    SELECT task_id FROM processed_tasks
    WHERE processed_at >= '2024-01-01'
      AND task_id IS NOT NULL
)
ORDER BY id
LIMIT 100;  -- バッチで 100 件ずつ処理

-- 特定のカテゴリを除いた商品（NOT IN + 定数リスト）
SELECT product_name, price
FROM products
WHERE category_id NOT IN (99, 100, 101)  -- 廃止カテゴリを除外
ORDER BY price;

-- 大量データでは NOT EXISTS か LEFT JOIN + IS NULL を検討
-- EXPLAIN で実行計画を確認する
EXPLAIN
SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

-- インデックスが効いているか確認（type = ALL はフルスキャン）
-- orders.customer_id にインデックスがなければ追加
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- NOT IN のリストが定数の場合はインデックスが効きやすい
-- サブクエリの場合はオプティマイザの判断による