当我得到不同的记录计数时,这个问题出现了,我认为是相同的查询,一个使用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返回一个结果。


当前回答

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

其他回答

如果你想用NOT IN对包含null的子查询进行过滤,只需检查NOT null

SELECT blah FROM t WHERE blah NOT IN
        (SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT NULL )

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

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

SQL使用三值逻辑来表示真值。IN查询产生预期的结果:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE col IN (NULL, 1)
-- returns first row

但是添加一个NOT并不会颠倒结果:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT col IN (NULL, 1)
-- returns zero rows

这是因为上面的查询与以下查询是等价的:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT (col = NULL OR col = 1)

下面是where子句的计算方法:

| col | col = NULL⁽¹⁾  | col = 1 | col = NULL OR col = 1 | NOT (col = NULL OR col = 1) |
|-----|----------------|---------|-----------------------|-----------------------------|
| 1   | UNKNOWN        | TRUE    | TRUE                  | FALSE                       |
| 2   | UNKNOWN        | FALSE   | UNKNOWN⁽²⁾            | UNKNOWN⁽³⁾                  |

注意:

涉及NULL的比较结果为UNKNOWN 没有一个操作数为TRUE且至少有一个操作数为UNKNOWN的OR表达式的结果为UNKNOWN (ref) UNKNOWN的NOT产生UNKNOWN(参考)

您可以将上面的示例扩展到两个以上的值(例如NULL, 1和2),但结果将是相同的:如果其中一个值为NULL,则没有行将匹配。

当你使用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。

这是给男孩的:

select party_code 
from abc as a
where party_code not in (select party_code 
                         from xyz 
                         where party_code = a.party_code);

不管ANSI设置如何,这都是有效的