回答。

問題の背景

ユーザーインターフェースによって値のリストが動的に設定されるフィルタリングの条件を形成する必要があることが多いです。また、欠損値（NULL）が結果に含まれるべきかどうかも管理する必要があります。このようなクエリは、大量のデータに対して高速に動作し、SQLのセマンティクスに関して正確でなければなりません。

問題

IN、NOT IN条件とNULLのチェックを不適切に組み合わせると、予期しない結果をもたらすことがあります。なぜなら、SQLにおいて「NULL IN (...)」は常にUNKNOWNを返し、「NOT IN」はリストの要素のうちの1つがNULLの場合、空の結果を生じるからです。適切に条件を書かないと、大量のデータに対するパフォーマンスが急激に低下します。

解決策

正しい組み合わせは以下のようになります：

リストから値を選択する場合、NULLも返す必要がある場合は条件を追加します：

SELECT * FROM sales
WHERE region IN ('Moscow','Samara') OR region IS NULL;

除外する場合は明示的なロジックを使用します：

SELECT * FROM sales
WHERE (region NOT IN ('Moscow','Samara') OR region IS NULL);

欠損値を厳密に除外するには：

SELECT * FROM sales
WHERE region NOT IN ('Moscow','Samara') AND region IS NOT NULL;

大規模なテーブルでは、特にリストによるフィルタリングが一般的な操作である場合、インデックスによるカバーが重要です。

主要な特徴：

NULLによるフィルタリングは明示的に管理する必要があります、さもなければ行を失うことになります。
INおよびNOT INの正しい使用は正確な選択にとって重要です。
IN/NOT INに関与するフィールドのインデックス作成はフィルタを大幅に高速化します。

罠のある質問。

「WHERE field NOT IN ('a', NULL)」を確認するとどうなりますか？

結果は常に空になります。なぜなら、リスト内のNULLと比較されるすべての行はUNKNOWNを返し、すべてがフィルタリングされるからです。

コードの例：

SELECT * FROM test WHERE name NOT IN ('Ivan', NULL);

「IN (NULL,...)」はNULLの行に値を与えますか？

いいえ。NULL IN (...)の式は常にUNKNOWNを返し、TRUEを返さないからです。

大きな値のリストでのフィルタリングを高速化するにはどうすればよいですか？

インデックスを使用し、リストが大きい場合（何百または何千の値）には、一時テーブルとのJOINを適用することを推奨します。これは、長いINに比べてフィルタリングを高速化します。

一般的な間違いやアンチパターン

NULLに対する明示的条件なしでIN/NOT INを記述すること
NULLを用いてNOT INを実行する際の動作の特異性を考慮しないこと
フィルタリングフィールド用にインデックスの最適化を行わないこと

実生活の例

ネガティブケース

分析レポートで「WHERE city NOT IN ('London', field_2, NULL)」を適用しました。結果は完全に空になり、誰も失われた行の原因を理解できませんでした。

長所：

短くて「読みやすい」クエリ

短所：

データの損失、開発者にとって分かりにくいロジック

ポジティブケース

CRMで顧客地域のリストを選択し、「欠損値を含む」ボタンを追加しました。フィルタのロジックは：

... WHERE city IN ('SPB','NNov') OR city IS NULL