sql - not in 和 in with null 值之间的区别

Question

我在我们的数据库中偶然发现了一些旧代码，它们的行为与预期不符。我设法将其缩小到 IN 子句的 NULL 值的影响。

我派生了一个证明它的测试:

declare @test   nvarchar(50) = '8620M/9010';
declare @testLT nvarchar(50) = '8004/9010';
declare @testLV nvarchar(50) =  null;  --'asd'
select case when @test not in (@testLT, @testLV) then 1 else 0 end

如果 testLV 为空，NOT IN 返回与 IN 子句相同的结果。

我想了解这种行为背后的机制。有人可以解释一下吗？

附言。我可能会将其替换为 EXCEPT 子句

Answer 1

NOT IN 的语义受列表中的 NULL 值的影响。在 SQL 中，NULL 表示“我不知道该值；它可以是任何值。”。

所以，如果你这样做:

where 1 in (1, 2)

然后它返回 true。

where 1 in (3, 4)

返回错误。

到目前为止，还不错。现在考虑:

where 1 not in (1, 2, NULL)

好吧，“1”显然在列表中。所以这会返回 false。

where 1 not in (3, 4, NULL)

嗯，“1”不是“3”或“4”。但它是 NULL 吗？我们不知道。因此，这将返回 NULL 而不是 true。并且 NULL 在 WHERE 条件中被视为与 false 相同，这意味着该行被过滤掉。 (请注意:check 条件的行为不同，只有 explicit false 未通过 check。)

因此，如果 任何 值是 NULL，则 NOT IN 不会返回任何内容。

这种情况几乎总是出现在子查询(而不是常量)的上下文中。出于这个原因，我建议在所有带有子查询的情况下使用 NOT EXISTS 而不是 NOT IN。