SQL NOT IN子句与NULL值的行为异常原因解析

问题背景 #

我需要对账两个数据库（Database A、Database B）中的table_a和table_b表。因Database B的ODBC查询缓慢，我在SQL Server Express中将两库配置为链接服务器，通过SELECT INTO生成table_a_copy和table_b_copy副本。

用于比对的键列排序规则不同：

• table_a_copy.key_a：Latin1_General_CI_AS_KS_WS
• table_b_copy.key_b：SQL_Latin1_General_CP1_CI_AS

问题现象 #

1. 初始NOT IN查询因排序规则冲突报错：

SELECT *
FROM table_b_copy
WHERE key_b NOT IN (SELECT key_a FROM table_a_copy)

错误信息：Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

2. 调整排序规则后的查询返回0结果，但确认table_b存在table_a没有的行：

SELECT *
FROM table_b_copy
WHERE key_b COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (
  SELECT key_a FROM table_a_copy
)

3. 过滤掉table_a_copy中某已知存在的key_a值后，查询返回了该值对应的行及预期的缺失行：

SELECT *
FROM table_b_copy
WHERE key_b COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (
  SELECT key_a
  FROM table_a_copy
  WHERE key_a <> 'some value'
)

4. 过滤key_a不为NULL的子查询则返回了正确的缺失行：

SELECT *
FROM table_b_copy
WHERE key_b COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (
  SELECT key_a
  FROM table_a_copy
  WHERE key_a IS NOT NULL
)

疑问 #

为什么在子查询中过滤NULL值或特定值后，NOT IN子句能正常工作？

核心原因：SQL中NOT IN的NULL处理逻辑 #

这是由NOT IN子句对NULL值的特殊处理规则导致的：

• SQL里的NULL代表“未知值”，任何与NULL的比较（=/<>）都会返回UNKNOWN，而非TRUE或FALSE。
• NOT IN的逻辑是：只有当目标值不等于子查询返回的所有值时，才会返回该行。如果子查询中存在NULL，那么对于每一行的判断都会变成key_b <> val1 AND key_b <> val2 AND ... AND key_b <> NULL——最后一个比较结果为UNKNOWN，整个AND表达式的结果也会变为UNKNOWN，SQL会将UNKNOWN视为FALSE，因此所有行都被过滤，返回0结果。

对应现象的具体解释 #

1. 调整排序规则后返回0结果：因为table_a_copy.key_a中存在NULL值，触发了上述NOT IN的NULL逻辑，导致所有行都被排除。
2. 过滤特定值后返回结果：当添加WHERE key_a <> 'some value'时，该条件间接排除了子查询中的所有NULL值（因为NULL <> 'some value'的结果是UNKNOWN，会被WHERE子句过滤），子查询不再返回NULL，NOT IN的逻辑恢复正常，能正确判断key_b是否不在子查询结果中。
3. 过滤key_a IS NOT NULL后返回正确结果：这直接移除了子查询中的所有NULL值，NOT IN回到常规逻辑——只要key_b不等于子查询里的所有非NULL值，就会被返回，自然得到预期的缺失行。

替代方案：使用NOT EXISTS避免NULL问题 #

如果不想手动处理NULL过滤，建议改用NOT EXISTS子句，它的NULL处理逻辑更直观，不会出现全量过滤的问题：

SELECT *
FROM table_b_copy b
WHERE NOT EXISTS (
  SELECT 1
  FROM table_a_copy a
  WHERE b.key_b COLLATE SQL_Latin1_General_CP1_CI_AS = a.key_a
)

NOT EXISTS仅检查是否存在匹配行，即使子查询中有NULL，只要没有匹配的非NULL行，就会返回对应的结果。

内容的提问来源于stack exchange，提问作者Pete