はじめに
こんにちは、@snowqueen_tgです。
SQLでは、JOINを使って複数のテーブルから関連するデータを取得できます。
では、次のような「関連するデータが存在しない行」を探したい場合は、どのように書けばよいのでしょうか。
- 一度も注文していない顧客
- 部署に所属していない従業員
- 商品が登録されていないカテゴリー
このような検索に使われる考え方が「Anti Join（アンチジョイン）」です。
今回は「注文履歴のない顧客」を例に、Anti Joinを実現する3つの書き方と注意点を整理してみました！
Anti Joinとは？
Anti Joinは、一方のテーブルに存在し、もう一方のテーブルには対応するデータが存在しない行を取得する処理です。
例えば、次の2つのテーブルがあるとします。
customers
| customer_id | customer_name |
|---|---|
| 1 | Tanaka |
| 2 | Sato |
| 3 | Suzuki |
orders
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 3 |
この場合、注文履歴がないのはcustomer_id = 2のSatoです。
ただし、一般的なSQLにはANTI JOINという構文が用意されているわけではありません。
主に次の方法で同じ処理を実現します。
NOT EXISTSNOT INLEFT JOIN ... IS NULL
NOT EXISTSを使う
最も意図が伝わりやすいのは、1NOT EXISTS1を使う方法です。
SELECT
c.customer_id,
c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
EXISTSは、サブクエリの結果が1行でも存在すればTRUEになります。
反対にNOT EXISTSは、条件に一致する行が存在しない場合にTRUEとなります。
この例では、各顧客について同じcustomer_idを持つ注文が存在するか確認し、存在しない顧客だけを取得しています。
結果は次のとおりです。
| customer_id | customer_name |
|---|---|
| 2 | Sato |
「注文が存在しない顧客を探す」という目的をSQLから読み取りやすく、関連する列にNULLが含まれていても影響を受けにくい書き方です。
NOT INを使う
同じ処理は、NOT INでも記述できます。
SELECT
customer_id,
customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
);
サブクエリが1と3を返す場合、それらに含まれない2が取得されます。
ただし、NOT INを使う場合はNULLに注意が必要です。
NULLが含まれている場合
例えば、サブクエリの結果にNULLが含まれているとします。
SELECT
customer_id,
customer_name
FROM customers
WHERE customer_id NOT IN (1, 3, NULL);
SQLにおけるNULLは、「値が存在しない」ではなく「不明」を表します。
そのため、customer_idがNULLと異なるかどうかを判定できず、条件全体がTRUEになりません。結果として、想定していた行が取得できない場合があります。
NOT INを使用するなら、対象列にNOT NULL制約が設定されているか確認するか、サブクエリ側でNULLを除外します。
SELECT
customer_id,
customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
WHERE customer_id IS NOT NULL
);
ただし、NULLが含まれる可能性がある場合は、最初からNOT EXISTSを使ったほうが意図を明確に表現できます。
LEFT JOINとIS NULLを使う
LEFT JOINとIS NULLを組み合わせる方法もあります。
SELECT
c.customer_id,
c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;
LEFT JOINでは、左側のcustomersにある行をすべて残します。
対応する注文がない場合、orders側の列にはNULLが入ります。そのため、WHERE o.customer_id IS NULLを指定すると、注文のない顧客だけを取得できます。
この方法はJOINに慣れている人には理解しやすい一方、IS NULLで確認する列の選び方には注意が必要です。
対応の有無を正しく判定できる、orders側のNOT NULL列を使用しましょう。
どの書き方を選ぶ？
3つの方法をまとめると
| 書き方 | 特徴 | 注意点 |
|---|---|---|
| NOT EXISTS | 処理の意図が分かりやすい | 相関条件の書き忘れに注意 |
| NOT IN | 短く書きやすい | サブクエリにNULLがあると結果が変わる |
| LEFT JOIN ... IS NULL | JOINとして理解しやすい | 判定に使う列と実行計画を確認する |
基本的には、「対応するデータが存在しない」という処理の意図を明確に表現でき、NULLの影響も受けにくいNOT EXISTSが使いやすいでしょう。
ただし、NOT EXISTS、NOT IN、LEFT JOIN ... IS NULLのどれが最も速いかは、データベースの種類やバージョン、データ量、インデックス、統計情報によって異なります。
オプティマイザによって同様のAnti Joinとして処理される場合もあるため、大量のデータを扱う際は、EXPLAINなどで実行計画を確認することが大切です。
まとめ
Anti Joinは、一方のテーブルに存在し、もう一方には対応するデータが存在しない行を探すための考え方です。
特に注意したいのが、NOT INとNULLの組み合わせです。対象列にNULLが含まれる可能性がある場合、想定した結果を取得できないことがあります。
迷った場合は、処理の意図が分かりやすく、NULLの影響も受けにくいNOT EXISTSから検討するとよいでしょう。
また、大量のデータを扱う場合は、SQLの書き方だけで判断せず、実行計画もあわせて確認することが重要です。
✨ちなみに、私が携わっているデータベース管理ツール「Navicat」では、SQLエディタを使ってクエリの作成・実行や結果の確認ができます。
製品のご購入をご希望の法人のお客様は、こちらをご利用ください。
TenGenesis株式会社日本総代理店：
https://japan-navicat.com/?utm_source=qiita&utm_medium=article
Navicat公式サイト：https://jp.navicat.com/
Linkedin：https://www.linkedin.com/company/navicat-japan/
X：https://x.com/navicat_jp