SQL Anti-JOIN -- マッチしない行を見つける

Anti-JOINとは？

Anti-JOINは、右テーブルにマッチがない左テーブルの行を返します。SQLには明示的なANTI JOINキーワードはありませんが、3つの同等のパターンで実現できます。

パターン1：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;

すべての顧客を保持し、結合で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
);

NOT EXISTSは短絡できます：マッチを1つ見つけるとすぐにordersテーブルの検索を停止するため、マッチが多い場合に効率的です。

パターン3：NOT IN（シンプルだがリスクあり）

SELECT c.id, c.name
FROM customers AS c
WHERE c.id NOT IN (
  SELECT customer_id FROM orders
);

警告：orders.customer_idにNULL値が含まれている場合、NOT INは空の結果を返します。x NOT IN (..., NULL, ...)は常にUNKNOWNになるためです。NOT EXISTSを使用するか、サブクエリにWHERE customer_id IS NOT NULLを追加してください。

パフォーマンス比較

パターン	NULL安全	短絡	プランタイプ
LEFT JOIN + IS NULL	はい	いいえ	Hash/Merge結合
NOT EXISTS	はい	はい	Anti Semi Join
NOT IN	いいえ*	場合による	様々

最新のPostgreSQLやSQL Serverのオプティマイザは、3つすべてに対して同じプランを生成することが多いです。MySQLは歴史的にNOT EXISTSを好んでいましたが、バージョン8+で改善されています。

適切なパターンの選択

デフォルトとしてNOT EXISTSを使用してください。NULL安全で、意図を明確に表現し、すべての主要データベースで良好なパフォーマンスを発揮します。右テーブルのカラムも他のフィルタリングに必要な場合はLEFT JOIN + IS NULLを使用してください。