SQL中IN子句内空查询为何有时被当作NULL处理？

首先明确两个查询的本质差异：

• 第一个查询 WHERE col NOT IN ((SELECT 1 WHERE 1=2))：子查询返回空集，NOT IN 空集意味着没有任何值需要排除，因此所有行都会被返回。
• 第二个查询 WHERE col NOT IN (1, (SELECT 1 WHERE 1=2))：这里的IN列表是1加上一个返回空集的子查询。SQL中，当IN列表包含一个返回空集的标量子查询时，该子查询会被视为NULL（标量子查询无返回值时等价于NULL）。而NOT IN中只要存在NULL，任何比较结果都会是UNKNOWN，导致所有行被过滤，最终返回空结果。

这不是认知误区，是SQL中NOT IN与NULL交互的标准行为：当NOT IN的集合中包含NULL时，col NOT IN (...) 等价于对集合中每个元素做col != x的AND操作，而col != NULL的结果是UNKNOWN，AND逻辑中只要有一个UNKNOWN，整体结果就是UNKNOWN，不会被WHERE条件匹配。

针对你的参数替换场景（空序列时需要让NOT IN (1, @Param)正常返回符合条件的行），可以采用以下几种方案：

方案1：统一使用子查询拼接，避免混合常量与参数占位符 #

将固定值和参数合并到同一个子查询中，比如把NOT IN (1, @Param)转换为：

SELECT *
FROM Tbl
WHERE col NOT IN (
    SELECT 1 -- 固定值
    UNION ALL
    SELECT val FROM @ParamTable -- 参数序列对应的表，空序列时此部分无数据
)

当参数为空序列时，子查询仅返回1，此时NOT IN会排除col=1的行，返回col=2的行，符合预期。

方案2：使用NOT EXISTS替代NOT IN #

NOT EXISTS对NULL的处理更直观，不会出现上述问题。改写查询为：

SELECT *
FROM Tbl t
WHERE t.col != 1
AND NOT EXISTS (
    SELECT 1 FROM @ParamTable p WHERE t.col = p.val
)

当参数为空序列时，NOT EXISTS条件始终为真，最终过滤掉col=1的行，返回col=2的行。

方案3：处理空参数时替换为永假条件 #

如果必须保留NOT IN的写法，当参数为空序列时，将@Param替换为一个永远不会匹配的值（比如超出col范围的数值），而不是空集子查询。例如：

• 正常参数序列：NOT IN (1, @Param1, @Param2)
• 空参数序列：NOT IN (1, -999999)（假设col不会取此值）

这样空参数时，NOT IN只会排除col=1和col=-999999的行，返回col=2的行。

使用你提供的测试表：

CREATE TABLE Tbl (col int NOT NULL);
INSERT INTO Tbl VALUES (1);
INSERT INTO Tbl VALUES (2);

用方案1的查询（参数为空时）：

SELECT *
FROM Tbl
WHERE col NOT IN (
    SELECT 1
    UNION ALL
    SELECT val FROM (SELECT 1 WHERE 1=2) AS EmptyParam
)

会返回col=2的行，符合预期。

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