【SQL】指定した値に一致しないデータを抽出する方法｜<>・NOT IN・NOT EXISTS・NOT LIKEの使い分け

-- 営業部以外の社員を取得
SELECT * FROM employees
WHERE department <> '営業';

⚠️ NULLに注意

id=6（伊藤直美）は department が NULL のため、結果に含まれません。NULLは <> で比較しても TRUE にならず、UNKNOWN（不明）と評価されるためです。NULLを含めたい場合は後述の対策が必要です。

-- どちらも同じ結果を返す
SELECT * FROM employees WHERE department <> '営業';
SELECT * FROM employees WHERE department != '営業';

-- 可搬性を考慮するなら <> を使うのが安全

-- 営業部と人事部以外の社員を取得
SELECT * FROM employees
WHERE department NOT IN ('営業', '人事');

⚠️ NOT IN と NULL の危険な組み合わせ

NOT IN のリストに NULL が含まれると、結果が0件になります。これはSQLで最もよくあるバグの一つです。

-- ❌ NOT IN に NULL が含まれると結果が0件になる
SELECT * FROM employees
WHERE department NOT IN ('営業', NULL);
-- 結果: 0件（全行がUNKNOWNと評価される）

-- 理由: NOT IN は内部的に AND で結合される
-- department <> '営業' AND department <> NULL
-- → NULLとの比較は常にUNKNOWN → 全体がUNKNOWN

-- ❌ サブクエリにNULLが混入するケースも危険
SELECT * FROM employees
WHERE department NOT IN (
  SELECT department FROM employees -- NULLが含まれる可能性あり
);
-- 結果: 0件になる可能性がある

-- ✅ 対策1: サブクエリでNULLを除外
SELECT * FROM employees
WHERE department NOT IN (
  SELECT department FROM employees
  WHERE department IS NOT NULL
);

-- ✅ 対策2: NOT EXISTS を使う（NULLに安全）
SELECT e1.* FROM employees e1
WHERE NOT EXISTS (
  SELECT 1 FROM other_table e2
  WHERE e1.department = e2.department
);

? Oracle の NOT IN 制限

Oracle では IN / NOT IN のリストに 1000件を超える値を指定できません。大量の値を除外する場合は一時テーブルと NOT EXISTS を使いましょう。詳しくは「IN句の1000件上限を回避する方法」を参照してください。

-- 注文が1件もない顧客を取得
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

-- 特定の部署に所属していない社員を取得
SELECT * FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM target_departments t
  WHERE t.dept_name = e.department
);

? 使い分けの目安

• 固定値リスト（NULLなし確実）→ NOT IN が簡潔
• サブクエリ（NULLの可能性あり）→ NOT EXISTS が安全
• 大量データ → NOT EXISTS のほうがパフォーマンスが良い傾向

-- メールアドレスが @example.com でない社員を取得
SELECT * FROM employees
WHERE email NOT LIKE '%@example.com';

-- 名前が「田」で始まらない社員を取得
SELECT * FROM employees
WHERE name NOT LIKE '田%';

-- 名前が3文字でない社員を取得（_ は任意の1文字）
SELECT * FROM employees
WHERE name NOT LIKE '___';

-- 複数のパターンを除外する場合は AND で結合
SELECT * FROM employees
WHERE email NOT LIKE '%@example.com'
  AND email NOT LIKE '%@test.com';

-- 給与が30万〜40万の範囲外の社員を取得
SELECT * FROM employees
WHERE salary NOT BETWEEN 300000 AND 400000;
-- 結果: 鈴木花子(450000)、高橋健太(500000)
-- ※ 伊藤直美(NULL)は含まれない

-- 上記は以下と同じ意味
SELECT * FROM employees
WHERE salary < 300000 OR salary > 400000;

-- 日付の範囲外を取得
SELECT * FROM orders
WHERE order_date NOT BETWEEN '2025-01-01' AND '2025-12-31';

-- メールアドレスが登録されている社員を取得
SELECT * FROM employees
WHERE email IS NOT NULL;
-- 結果: id=1,2,3,4,6 の5件

-- ❌ これは間違い（結果が常に0件になる）
SELECT * FROM employees
WHERE email <> NULL;
-- 結果: 0件（NULLとの比較は常にUNKNOWN）

-- 複数カラムのNULLチェックを組み合わせ
SELECT * FROM employees
WHERE department IS NOT NULL
  AND email IS NOT NULL;
-- 結果: id=1,2,3,4 の4件

-- MySQL以外（PostgreSQL, SQL Server, SQLite）
SELECT * FROM employees
EXCEPT
SELECT * FROM employees WHERE department = '営業';

-- Oracle の場合は MINUS を使用
SELECT * FROM employees
MINUS
SELECT * FROM employees WHERE department = '営業';

-- ❌ NULLの行が取得できない
SELECT * FROM employees
WHERE department <> '営業';
-- 結果: 3件（伊藤直美が含まれない）

-- ✅ NULLも含めて取得する
SELECT * FROM employees
WHERE department <> '営業'
   OR department IS NULL;
-- 結果: 4件（伊藤直美も含まれる）

-- ✅ COALESCE を使う方法
SELECT * FROM employees
WHERE COALESCE(department, '') <> '営業';
-- NULLが空文字に変換されるため、全行が比較対象になる
-- ※ 空文字のdepartmentが存在する場合も取得されるので注意

-- ✅ MySQL限定: <=> 演算子（NULL安全等価）の否定
SELECT * FROM employees
WHERE NOT (department <=> '営業');
-- <=> はNULL同士も比較可能。NOT と組み合わせるとNULLの行も取得できる

-- 営業部以外 かつ 給与40万以上 かつ メールがNULLでない
SELECT * FROM employees
WHERE department <> '営業'
  AND salary >= 400000
  AND email IS NOT NULL;
-- 結果: 鈴木花子（開発, 450000）

-- 営業部でも人事部でもなく、給与が30万〜40万の範囲外
SELECT * FROM employees
WHERE department NOT IN ('営業', '人事')
  AND salary NOT BETWEEN 300000 AND 400000;

-- 注文がない顧客を取得（LEFT JOIN パターン）
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

-- 上記と同じ結果（NOT EXISTS パターン）
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

? LEFT JOIN vs NOT EXISTS

• パフォーマンスはRDBMSやデータ量によって異なる
• 可読性は NOT EXISTS のほうが意図が明確
• MySQL では LEFT JOIN + IS NULL が速い傾向、PostgreSQL では NOT EXISTS が速い傾向

-- 営業部以外の社員を削除
DELETE FROM employees
WHERE department <> '営業'
   OR department IS NULL;

-- 特定のIDリスト以外を削除
DELETE FROM employees
WHERE id NOT IN (1, 3, 5);

-- 営業部以外の社員に10%昇給
UPDATE employees
SET salary = salary * 1.1
WHERE department <> '営業'
  AND salary IS NOT NULL;

-- 平均給与が40万未満の部署を除外して表示
SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS cnt
FROM employees
WHERE department IS NOT NULL
GROUP BY department
HAVING AVG(salary) >= 400000;

-- 社員が1人しかいない部署を除外
SELECT department, COUNT(*) AS cnt
FROM employees
WHERE department IS NOT NULL
GROUP BY department
HAVING COUNT(*) <> 1;