NOT IN子句中的空值

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

323450 次浏览

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

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

< p >。SELECT 'true' where 3 in (1,2,null) 而且 SELECT 'true' where 3 not in (1,2,null)

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

在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是一个未知值,在可能值列表中包含NULLNULLNOT 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为真,因此谓词的计算结果为真,并得到一行。

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

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

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

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

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

这也可能用于了解join、exists和in之间的逻辑差异 http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx < / p >

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

UNKNOWN不等于FALSE 你可以通过调用

来测试它
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设置如何,这都是有效的

在撰写本文时,这个问题的标题是

SQL NOT IN约束和NULL值

从问题的文本来看,问题似乎发生在SQL DML SELECT查询中,而不是SQL DDL CONSTRAINT中。

然而,特别是考虑到标题的措辞,我想指出这里所做的一些陈述是潜在的误导性陈述,那些沿着(意译)

当谓词求值为UNKNOWN时,您不会得到任何行。

尽管这是SQL DML的情况,但当考虑约束时,效果是不同的。

考虑一下这个非常简单的表,其中有两个约束条件直接来自问题中的谓词(@Brannon的回答很好):

DECLARE @T TABLE
(
true CHAR(4) DEFAULT 'true' NOT NULL,
CHECK ( 3 IN (1, 2, 3, NULL )),
CHECK ( 3 NOT IN (1, 2, NULL ))
);


INSERT INTO @T VALUES ('true');


SELECT COUNT(*) AS tally FROM @T;

根据@Brannon的回答,第一个约束(使用IN)计算为TRUE,第二个约束(使用NOT IN)计算为UNKNOWN。然而,插入成功!因此,在这种情况下,严格地说“您没有得到任何行”是不正确的,因为我们确实得到了一行插入作为结果。

上述效果对于SQL-92标准来说确实是正确的。比较和对比SQL-92规范中的以下部分

7.6 where条款

的结果是T的那些行表 哪个搜索条件的结果是真

4.10完整性约束

表检查约束满足当且仅当指定

.表中任何一行的查询条件都不为false

换句话说:

在SQL DML中,当WHERE计算为UNKNOWN时,将从结果中删除行,因为它满足条件“为真”。

在SQL DDL(即约束)中,当行计算为UNKNOWN时,不会从结果中删除,因为满足条件“不为假”。

尽管在SQL DML和SQL DDL中的效果分别看起来可能是矛盾的,但有一个实际的理由让UNKNOWN结果“受益于怀疑”,允许它们满足约束(更准确地说,允许它们不满足约束):没有这种行为,每个约束都必须显式地处理空值,从语言设计的角度来看,这将是非常令人不满意的(更不用说,对程序员来说是一个痛苦!)

附注:如果你发现像我写的那样遵循“未知不满足约束”这样的逻辑具有挑战性,那么你可以考虑通过避免SQL DDL中可空列和SQL DML中任何产生空值的列(例如外部连接)来省去这一切!

从这里的答案可以得出结论,NOT IN (subquery)不能正确处理空值,应该避免使用NOT EXISTS。然而,这样的结论可能为时过早。在以下场景中,由Chris Date(数据库编程与设计,第2卷第9期,1989年9月),是NOT IN正确处理空值并返回正确结果,而不是NOT EXISTS

考虑一个表sp来表示已知供应数量(qty)的零件(pno)的供应商(sno)。该表当前保存以下值:

      VALUES ('S1', 'P1', NULL),
('S2', 'P1', 200),
('S3', 'P1', 1000)

注意,数量是可空的,即能够记录已知供应商供应零件的事实,即使不知道数量是多少。

任务是找到已知供应部件编号为“P1”但数量不是1000的供应商。

下面使用NOT IN仅正确识别供应商'S2':

WITH sp AS
( SELECT *
FROM ( VALUES ( 'S1', 'P1', NULL ),
( 'S2', 'P1', 200 ),
( 'S3', 'P1', 1000 ) )
AS T ( sno, pno, qty )
)
SELECT DISTINCT spx.sno
FROM sp spx
WHERE spx.pno = 'P1'
AND 1000 NOT IN (
SELECT spy.qty
FROM sp spy
WHERE spy.sno = spx.sno
AND spy.pno = 'P1'
);

然而,下面的查询使用了相同的一般结构,但使用了NOT EXISTS,但在结果中错误地包含了供应商'S1'(即其数量为空):

WITH sp AS
( SELECT *
FROM ( VALUES ( 'S1', 'P1', NULL ),
( 'S2', 'P1', 200 ),
( 'S3', 'P1', 1000 ) )
AS T ( sno, pno, qty )
)
SELECT DISTINCT spx.sno
FROM sp spx
WHERE spx.pno = 'P1'
AND NOT EXISTS (
SELECT *
FROM sp spy
WHERE spy.sno = spx.sno
AND spy.pno = 'P1'
AND spy.qty = 1000
);

所以NOT EXISTS不是银弹,它可能已经出现了!

当然,问题的根源是空值的存在,因此“真正的”解决方案是消除这些空值。

这可以通过以下两个表来实现(在其他可能的设计中):

  • sp供应商已知的零件供应
  • spq供应商已知供应已知数量的部件

注意应该有一个外键约束,其中spq引用sp

然后可以使用'minus'关系运算符(在标准SQL中是EXCEPT关键字)来获得结果。

WITH sp AS
( SELECT *
FROM ( VALUES ( 'S1', 'P1' ),
( 'S2', 'P1' ),
( 'S3', 'P1' ) )
AS T ( sno, pno )
),
spq AS
( SELECT *
FROM ( VALUES ( 'S2', 'P1', 200 ),
( 'S3', 'P1', 1000 ) )
AS T ( sno, pno, qty )
)
SELECT sno
FROM spq
WHERE pno = 'P1'
EXCEPT
SELECT sno
FROM spq
WHERE pno = 'P1'
AND qty = 1000;

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

SELECT blah FROM t WHERE blah NOT IN
(SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT 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⁽³⁾                  |

注意:

  1. 涉及NULL的比较得到UNKNOWN
  2. 如果没有一个操作数是TRUE,且至少有一个操作数是UNKNOWN,则OR表达式将生成UNKNOWN (裁判)
  3. UNKNOWNNOT产生UNKNOWN (裁判)

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