データベースを扱っていると、「このテーブルにあって、あのテーブルにないデータは何？」という疑問が出てくることがあります。一見簡単そうに思えますが、実は「存在しないものを見つける」のは、SQLの中でも少しトリッキーな操作なんです。
たとえば、こんな場面を想像してみてください。
- 「注文していない顧客を抽出したい」
- 「まだレビューが付いていない商品を見つけたい」
- 「社員リストにあって、出勤記録にない人を確認したい」
これらは全て「存在しないものを探す」クエリです。普通のSELECT文では「存在するもの」を取得するので、逆の発想が必要になります。
この記事では、SQLで「存在しないデータ」を抽出する3つの主要な方法と、それぞれの使い分けを分かりやすく解説していきます。
基本的な考え方を理解しよう
具体的な方法を学ぶ前に、基本的な考え方を押さえておきましょう。
「存在する」の反対は「存在しない」
当たり前のように聞こえますが、これがポイントです。SQLでは「存在するもの」を見つけるのは簡単ですが、「存在しないもの」を見つけるには工夫が必要です。
たとえば、顧客テーブルに100人のデータがあって、注文テーブルには80人分の注文があるとします。この時、「注文していない20人」を見つけるには、全顧客から注文済み顧客を除外する必要があります。
サンプルデータで考えてみる
以下のような2つのテーブルがあるとします。
customersテーブル（顧客）
customer_id | customer_name
------------|---------------
1 | 田中太郎
2 | 佐藤花子
3 | 鈴木一郎
4 | 高橋美咲
5 | 伊藤健太ordersテーブル（注文）
order_id | customer_id | amount
---------|-------------|--------
101 | 1 | 5000
102 | 1 | 3000
103 | 3 | 8000
104 | 4 | 2000この例では、customer_id が 2（佐藤花子）と 5（伊藤健太）の顧客が注文をしていません。これを抽出するのが今回のテーマです。
3つの主要なアプローチ
SQLで「存在しないもの」を抽出する方法は主に3つあります。
- NOT IN句：「このリストに含まれないもの」という条件
- NOT EXISTS句：「この条件に該当するデータが存在しないもの」という条件
- LEFT JOIN + IS NULL：「結合してマッチしなかったもの」を見つける
それぞれ見ていきましょう。
方法1：NOT IN句を使った抽出
NOT IN句は、最も直感的で分かりやすい方法です。
NOT IN句の基本構文
SELECT column_name
FROM table1
WHERE column_name NOT IN (SELECT column_name FROM table2);「table2に存在しない値を持つtable1のデータ」を取得します。
実例：注文していない顧客を抽出
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
);実行結果
customer_id | customer_name
------------|---------------
2 | 佐藤花子
5 | 伊藤健太注文履歴がない顧客だけが抽出されました。
NOT IN句の動作原理
内側のSELECT文（サブクエリ）で、注文テーブルから顧客IDのリストを作ります。
(1, 1, 3, 4) -- 注文した顧客のIDリスト外側のSELECT文で、このリストに含まれない顧客を探します。結果として、2と5が抽出されるわけです。
NOT IN句の注意点
NOT IN句には重要な注意点があります。それはNULLの扱いです。
-- 問題のあるクエリ
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id -- この中にNULLが含まれていたら？
FROM orders
);もしordersテーブルのcustomer_idにNULLが1つでも含まれていると、このクエリは何も返しません。
なぜなら、SQLではNULLとの比較結果は「不明」となり、NOT INの条件が正しく評価されないためです。
対策
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
WHERE customer_id IS NOT NULL -- NULLを除外
);サブクエリ内でNULLを明示的に除外することで、安全に使えます。
方法2：NOT EXISTS句を使った抽出
NOT EXISTS句は、より柔軟で強力な方法です。
NOT EXISTS句の基本構文
SELECT column_name
FROM table1 t1
WHERE NOT EXISTS (
SELECT 1
FROM table2 t2
WHERE t2.column_name = t1.column_name
);「table1の各行について、条件に一致するtable2のデータが存在しないもの」を取得します。
実例：注文していない顧客を抽出
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);実行結果
customer_id | customer_name
------------|---------------
2 | 佐藤花子
5 | 伊藤健太NOT IN句と同じ結果が得られました。
NOT EXISTS句の動作原理
customersテーブルの各行に対して、以下をチェックします。
- 田中太郎（ID=1）：ordersに存在する → 除外
- 佐藤花子（ID=2）：ordersに存在しない → 抽出
- 鈴木一郎（ID=3）：ordersに存在する → 除外
- 高橋美咲（ID=4）：ordersに存在する → 除外
- 伊藤健太（ID=5）：ordersに存在しない → 抽出
「存在しない」という条件に一致した行だけが結果に含まれます。
SELECT 1 の意味
サブクエリ内のSELECT 1に違和感を持つかもしれません。
SELECT 1 -- これは何？
FROM orders o
WHERE o.customer_id = c.customer_id実は、NOT EXISTSでは「何が返ってくるか」ではなく「何か返ってくるかどうか」だけが重要です。データが存在すれば1を返し、存在しなければ何も返しません。
SELECT *でもSELECT customer_idでも動作しますが、SELECT 1が最も効率的です。データベースは値を取得する必要がなく、存在だけをチェックすればいいからです。
NOT EXISTS句の利点
NOT EXISTS句には、NOT IN句にない利点があります。
NULLに対して安全
NULLが含まれていても、正しく動作します。WHERE句の比較でNULLは自動的に除外されるためです。
複雑な条件を書きやすい
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= '2024-01-01' -- 複数の条件を追加できる
AND o.amount >= 5000
);「2024年以降に5000円以上の注文をしていない顧客」といった複雑な条件も書けます。
方法3：LEFT JOINとIS NULLを使った抽出
LEFT JOINを使う方法は、視覚的に理解しやすいアプローチです。
LEFT JOINの基本構文
SELECT t1.column_name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.key = t2.key
WHERE t2.key IS NULL;「table1とtable2を結合して、マッチしなかった行」を取得します。
LEFT JOINとは
LEFT JOIN（左外部結合）は、左側のテーブルの全ての行を保持しながら、右側のテーブルと結合します。マッチするデータがない場合、右側の列はNULLになります。
イメージ図
customers (左) LEFT JOIN orders (右)
-------------- -----------
ID=1 田中 → 注文あり
ID=2 佐藤 → NULL（注文なし）
ID=3 鈴木 → 注文あり
ID=4 高橋 → 注文あり
ID=5 伊藤 → NULL（注文なし）右側がNULLの行が、「注文していない顧客」というわけです。
実例：注文していない顧客を抽出
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;実行結果
customer_id | customer_name
------------|---------------
2 | 佐藤花子
5 | 伊藤健太同じ結果が得られました。
DISTINCTが必要な場合
注意点として、一人の顧客が複数回注文している場合、LEFT JOINでは重複が発生する可能性があります。
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;DISTINCTを付けることで、重複を除去できます。ただし、今回のケースではordersにデータがない行を探しているので、実際には重複は発生しません。
LEFT JOINの利点
LEFT JOINを使う方法には、独特の利点があります。
結合データも一緒に取得できる
SELECT c.customer_id, c.customer_name,
COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) = 0;集計と組み合わせて、より詳細な分析ができます。
視覚的に分かりやすい
「左のテーブルを全部残して、右のテーブルと繋げる」という考え方は直感的です。
3つの方法のパフォーマンス比較
どの方法が最も高速でしょうか？実は、状況によって異なります。
NOT IN句のパフォーマンス
メリット
- シンプルで分かりやすい
- 小規模データでは十分高速
デメリット
- サブクエリの結果を全て読み込む必要がある
- NULLがあると正しく動作しない
- 大量データでは遅くなる可能性
推奨される場面
- データ量が少ない（数千件程度）
- サブクエリの結果にNULLが絶対に含まれない
NOT EXISTS句のパフォーマンス
メリット
- 最も効率的な場合が多い
- 条件に合うデータが見つかった時点で次の行に進める（ショートサーキット）
- NULLに対して安全
デメリット
- 構文がやや複雑
推奨される場面
- 大量データを扱う
- 複雑な条件を指定したい
- パフォーマンスが重要
LEFT JOIN + IS NULLのパフォーマンス
メリット
- 視覚的に理解しやすい
- 集計と組み合わせやすい
- インデックスが効きやすい
デメリット
- 結合処理のオーバーヘッドがある
- 重複データがある場合はDISTINCTが必要
推奨される場面
- 結合後のデータも使いたい
- 集計処理と組み合わせたい
- チーム内でJOINに慣れている
実際の速度比較
多くのデータベースでは、最適化エンジンが内部的に最適な実行計画を選びます。そのため、NOT EXISTSとLEFT JOINの速度差はほとんどないことが多いです。
ただし、一般的には以下の傾向があります。
速い ← → 遅い
NOT EXISTS ≒ LEFT JOIN > NOT IN特に大量データ（数十万件以上）では、NOT EXISTSが最も安定して高速です。
実践的な使用例
実際のプロジェクトで使える具体的な例を紹介します。
例1：購入していない商品を見つける
顧客が購入していない商品カテゴリを抽出します。
-- 方法1：NOT IN
SELECT category_name
FROM categories
WHERE category_id NOT IN (
SELECT DISTINCT p.category_id
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.customer_id = 123
AND p.category_id IS NOT NULL
);
-- 方法2：NOT EXISTS
SELECT cat.category_name
FROM categories cat
WHERE NOT EXISTS (
SELECT 1
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.customer_id = 123
AND p.category_id = cat.category_id
);どちらの方法でも、顧客ID=123の人が買ったことのないカテゴリが分かります。
例2：レビューがない商品を抽出
SELECT p.product_id, p.product_name, p.price
FROM products p
LEFT JOIN reviews r ON p.product_id = r.product_id
WHERE r.review_id IS NULL
ORDER BY p.price DESC;レビューがない商品を、価格が高い順に表示します。レビュー依頼の対象商品を選ぶ時に便利です。
例3：出勤していない社員を確認
SELECT e.employee_id, e.employee_name, e.department
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM attendance a
WHERE a.employee_id = e.employee_id
AND a.attendance_date = CURRENT_DATE
)
AND e.employment_status = '在職中';今日の日付で出勤記録がない、かつ在職中の社員を抽出します。
例4：メールを開封していないユーザー
SELECT u.user_id, u.email, u.name
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM email_opens eo
WHERE eo.user_id = u.user_id
AND eo.email_id = 2024001 -- 特定のメールキャンペーン
)
AND u.subscribed = TRUE;メールキャンペーンで、まだメールを開封していない購読中のユーザーを見つけます。
例5：条件付きの存在確認
-- 最近3ヶ月間に購入していない顧客
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
);期間を指定した「存在しない」条件も簡単に書けます。
よくある質問と回答
「存在しないデータ」の抽出について、よくある質問をまとめました。
Q1：NOT INとNOT EXISTS、どちらを使うべき？
一般的にはNOT EXISTSをおすすめします。理由は以下の通りです。
- NULLに対して安全
- パフォーマンスが安定している
- 複雑な条件を書きやすい
ただし、シンプルな条件でNULLの心配がない場合は、NOT INの方が読みやすいこともあります。
Q2：LEFT JOINでWHERE句の条件はどこに書くべき？
これは重要なポイントです。
間違った例
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2024-01-01' -- ここに書くと意味が変わる
AND o.customer_id IS NULL;これは常に0件を返します。なぜなら、order_dateがNULLの行に対して日付比較をしているからです。
正しい例
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
AND o.order_date > '2024-01-01' -- 結合条件に含める
WHERE o.customer_id IS NULL;結合条件に含めることで、「2024年以降の注文がない顧客」を正しく抽出できます。
Q3：複数のテーブルで存在チェックしたい場合は？
複数のNOT EXISTSを組み合わせます。
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
SELECT 1
FROM inquiries i
WHERE i.customer_id = c.customer_id
);「注文もなく、問い合わせもない顧客」を抽出できます。
Q4：「AまたはBに存在しない」という条件は？
ANDではなくORで繋ぎます。
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.product_id = p.product_id
)
OR NOT EXISTS (
SELECT 1
FROM inventory i
WHERE i.product_id = p.product_id
AND i.stock > 0
);「注文されたことがないか、在庫がない商品」を抽出します。
Q5：パフォーマンスを改善するには？
以下の点をチェックしましょう。
インデックスの確認
-- 結合キーや条件に使う列にインデックスを作成
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);EXPLAIN文で実行計画を確認
EXPLAIN SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);どのインデックスが使われているか、何行スキャンしているかが分かります。
特殊なケース：差集合を求める
データベースによっては、EXCEPT（またはMINUS）演算子が使えます。
EXCEPT演算子の使い方
-- PostgreSQL、SQL Serverの場合
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;これは「customersにあってordersにないcustomer_id」を返します。シンプルで分かりやすいですね。
対応状況
- PostgreSQL：EXCEPT
- SQL Server：EXCEPT
- Oracle：MINUS
- MySQL：未対応（8.0以降でも）
MySQLでは使えないため、NOT IN、NOT EXISTS、LEFT JOINのいずれかを使う必要があります。
ベストプラクティスとまとめ
「存在しないデータ」を抽出する時のベストプラクティスをまとめます。
推奨される使い分け
シンプルな条件 + 小規模データ
WHERE column NOT IN (SELECT column FROM table)ただし、NULLがないことを確認してください。
複雑な条件 + 大規模データ
WHERE NOT EXISTS (SELECT 1 FROM table WHERE ...)最も汎用性が高く、パフォーマンスも良好です。
集計や追加データ取得が必要
LEFT JOIN table ON ... WHERE table.key IS NULL結合後のデータも活用できます。
チェックリスト
- [ ] サブクエリの結果にNULLが含まれないか確認
- [ ] 適切なインデックスが設定されているか確認
- [ ] WHERE句とON句の条件を正しく分けているか確認
- [ ] 必要に応じてDISTINCTを使用しているか確認
- [ ] パフォーマンステストを実施したか確認
最後に
「存在しないもの」を探すクエリは、最初は難しく感じるかもしれません。でも、3つの方法（NOT IN、NOT EXISTS、LEFT JOIN）の特徴を理解すれば、どんな場面でも適切に使い分けられるようになります。
重要なポイントをおさらい
- NOT IN：シンプルだがNULLに注意
- NOT EXISTS：最も安全で高速、複雑な条件も書ける
- LEFT JOIN：視覚的で集計と相性が良い
実務では、NOT EXISTSを基本として、状況に応じてLEFT JOINを使うのが良いでしょう。NOT INは、NULLがないことが保証されている場合のみ使用してください。
これらの技術をマスターすれば、データ分析の幅が大きく広がります。「購入していない顧客」「使われていない機能」「未対応の問い合わせ」など、様々な「存在しないもの」を見つけ出せるようになるでしょう。
ぜひ実際のプロジェクトで試してみてくださいね！
コメント