NOT IN子句中的空值

当我得到不同的记录计数时，这个问题出现了，我认为是相同的查询，一个使用not in where约束，另一个使用左连接。not in约束中的表有一个空值(坏数据)，导致该查询返回0条记录计数。我有点理解为什么，但我需要一些帮助来充分理解这个概念。

简单地说，为什么查询A返回结果而B没有?

A: select 'true' where 3 in (1, 2, 3, null)
B: select 'true' where 3 not in (1, 2, null)

这是在SQL Server 2005上。我还发现调用set ansi_nulls off会导致B返回一个结果。

当前回答

Null表示数据的缺失，即它是未知的，而不是一个无数据值。具有编程背景的人很容易混淆这一点，因为在C类型语言中，当使用指针时，null实际上是空的。

因此，在第一种情况下，3确实在(1,2,3,null)的集合中，因此返回true

在第二种情况下，你可以把它简化为

选择“true”where 3 not in (null)

所以没有返回任何东西，因为解析器不知道与之进行比较的集合——它不是一个空集，而是一个未知集。使用(1,2,null)没有帮助因为(1,2)集合显然是假的，但是你用它来对抗未知，它是未知的。

其他回答

Compare to null没有定义，除非你使用is null。

因此，当比较3和NULL(查询A)时，它返回undefined。

例如，SELECT 'true' where 3 in (1,2,null) 而且 SELECT 'true' where 3 not in (1,2,null)

将产生相同的结果，因为NOT (UNDEFINED)仍然是UNDEFINED，但不是TRUE

当你使用NULL时，你实际上是在处理一个三值逻辑。

你的第一个查询返回WHERE子句的结果:

    3 = 1 or 3 = 2 or 3 = 3 or 3 = null
which is:
    FALSE or FALSE or TRUE or UNKNOWN
which evaluates to 
    TRUE

第二点:

    3 <> 1 and 3 <> 2 and 3 <> null
which evaluates to:
    TRUE and TRUE and UNKNOWN
which evaluates to:
    UNKNOWN

“未知”并不等同于“虚假” 你可以通过调用:

select 'true' where 3 <> null
select 'true' where not (3 <> null)

这两个查询都不会得到结果

如果UNKNOWN与FALSE相同，那么假设第一个查询会给你FALSE，那么第二个查询就必须计算为TRUE，因为它与NOT(FALSE)相同。事实并非如此。

在SqlServerCentral上有一篇关于这个主题的非常好的文章。

null和三值逻辑的整个问题一开始可能有点令人困惑，但为了在TSQL中编写正确的查询，理解它是必不可少的

我要推荐的另一篇文章是SQL聚合函数和NULL。

在A中，3对集合中的每个成员进行相等性测试，结果是(FALSE, FALSE, TRUE, UNKNOWN)。因为其中一个元素为TRUE，条件也为TRUE。(也有可能在这里发生了一些短路，所以它实际上在遇到第一个TRUE时就停止了，并且从不计算3=NULL。)

在B中，我认为它将条件评估为NOT (3 In (1,2,null))。测试3是否对集合产生相等的结果(FALSE, FALSE, UNKNOWN)，它被聚合到UNKNOWN。NOT (UNKNOWN)产生未知。所以总的来说，情况的真相是未知的，最终基本上被视为错误。

当与未知值比较时，NOT IN返回0条记录

由于NULL是一个未知值，在可能值列表中包含NULL或NULLs的NOT IN查询将总是返回0记录，因为没有办法确保NULL值不是正在测试的值。

查询A相当于:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

因为3 = 3为真，你得到一个结果。

查询B为:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

当ansi_nulls打开时，3 <> null为UNKNOWN，因此谓词的计算结果为UNKNOWN，并且您不会得到任何行。

当ansi_nulls关闭时，3 <> null为真，因此谓词的计算结果为真，并得到一行。

推荐文章