SQL JOINのNULL処理 -- 予期しない結果の回避

NULLとJOIN：隠れた罠

SQLのNULLは不明な値を表します。何とも等しくありません。別のNULLとも等しくありません。これは結合の動作に重大な影響を与えます。

NULLは結合条件で決してマッチしない

-- これらのNULLは互いにマッチしない
Table A: id = NULL
Table B: id = NULL

-- a.id = b.idはNULL（TRUEではなく）と評価され、結合は発生しない

つまり：

• INNER JOIN：NULL結合キーを持つ行は結果から除外されます。
• LEFT/RIGHT JOIN：NULLキー行は表示されますが、もう一方でマッチを見つけることはありません。
• NOT IN：サブクエリ内の1つのNULLで結果全体が空になることがあります。

NOT INのNULLの罠

-- orders.customer_idにNULLが含まれている場合：
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- ゼロ行を返す！期待されるAnti-JOINの結果ではない。

これは5 NOT IN (1, 2, NULL)がUNKNOWNと評価され、SQLがこれをFALSEとして扱うためです。常にNOT EXISTSを使用するか、WHERE customer_id IS NOT NULLを追加してください。

NULL安全な比較演算子

異なるデータベースがNULL安全な等価演算子を提供しています：

データベース	演算子	例
PostgreSQL	IS NOT DISTINCT FROM	a.id IS NOT DISTINCT FROM b.id
MySQL	<=>	a.id <=> b.id
SQLite	IS	a.id IS b.id

COALESCEの回避策

NULL安全な演算子が利用できないか読みにくい場合、COALESCEを使用してNULLをセンチネル値に変換します：

ON COALESCE(a.region, '__NONE__') = COALESCE(b.region, '__NONE__')

注意：これはインデックスの使用を妨げ、データにセンチネル値が存在する場合に誤マッチを引き起こす可能性があります。

ベストプラクティス

1. 可能な限り、スキーマで結合カラムをNOT NULLとして宣言する。
2. Anti-JOINにはNOT INの代わりにNOT EXISTSを使用する。
3. 外部結合の後のWHERE句でNULLを明示的に処理する。
4. 結合カラムにNULL値を入れてクエリをテストし、意外な結果を発見する。