回答。

问题背景

经常需要生成查询，其中筛选条件是动态确定的——值列表由用户界面提供，而缺失值（NULL）也必须能够进入或不进入结果。这样的查询必须在大数据量下快速执行并在SQL语义上是正确的。

问题

不正确地组合条件IN，NOT IN和NULL检查会导致意外结果，因为在SQL中表达式"NULL IN (...)"始终返回UNKNOWN，而"NOT IN"可能导致空结果，如果列表中的任何元素是NULL。对于大数据而言，条件写得不好时，性能可能急剧下降。

解决方案

正确的组合方式如下：

• 如果从列表中选择值，当需要返回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语句更快。

常见错误和反模式

• 只使用IN/NOT IN而不显式地针对NULL设置条件
• 不考虑NOT IN与NULL的执行特性
• 不优化用于筛选字段的索引

生活中的例子

负面案例

在分析报告中应用了"WHERE city NOT IN ('London', field_2, NULL)"。结果完全为空，没人能及时理解丢失行的原因。

优点：

• 简短且"可读"的查询

缺点：

• 数据丢失，对于开发者来说逻辑不明显

正面案例

在CRM中选择了客户地区列表并添加了"包含缺失值"的额外按钮。筛选逻辑：

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

优点：

• 灵活的选择，正确考虑数据的不完整性
• 由于索引化快速运行

缺点：

• 在每次模型更改时都需要测试逻辑