NOT IN子句和NULL值

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

1> Brannon..：

---

查询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则为true,因此谓词的计算结果为true,并且您得到一行.

---

有没有人曾经指出将`NOT IN`转换为一系列`<>和`会改变*不在此集*中的语义行为？

---

@Ian - 看起来像"A NOT IN('X','Y')"实际上是SQL中A <>'X'和A <>'Y'的别名.(我看到你自己在http://stackoverflow.com/questions/3924694/sql-server-why-does-comparison-null-value-return-true-for-not-in中发现了这一点,但是想确定一下你的反对意见已在这个问题中得到解决.)

---

这是SQL服务器的一种非常糟糕的行为,因为如果它期望使用"IS NULL"进行NULL比较,那么它应该将IN子句扩展为相同的行为,而不是愚蠢地将错误的语义应用于自身.

2> kristof..：

---

无论何时使用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.

3> YonahW..：

---

NOT IN 与未知值进行比较时返回0条记录

由于NULL是未知的,在可能值列表中NOT IN包含a NULL或NULLs 的查询将始终返回0记录,因为无法确定该NULL值不是正在测试的值.

---

简而言之，这就是答案。我发现即使没有任何示例，也更容易理解。

4> Sunny Mileno..：

---

除非使用IS NULL,否则比较null是未定义的.

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

即SELECT'true'其中3 in(1,2,null)和SELECT'true'其中3 in in(1,2,null)

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

5> onedaywhen..：

---

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

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,第二个约束(using NOT IN)求值为UNKNOWN.但是,插入成功!因此,在这种情况下,说"你没有得到任何行"并不严格正确,因为我们确实插入了一行作为结果.

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

7.6 where子句

结果是T的那些行的表,其搜索条件的结果为真.

4.10完整性约束

当且仅当指定的搜索条件对于表的任何行不为假时,才满足表检查约束.

换一种说法:

在SQL DML,行从结果中删除时,WHERE计算结果为未知的,因为它不符合条件"是真的".

在SQL DDL(即约束)中,当它们计算为UNKNOWN时,不会从结果中删除行,因为它 确实满足条件"不是假".

虽然SQL DML和SQL DDL中的效果分别可能看似矛盾,但实际上有理由通过允许UNKNOWN结果满足约束(更准确地说,允许它们不能满足约束)来给予UNKNOWN结果"怀疑的好处". :没有这种行为,每个约束都必须显式处理空值,从语言设计的角度来看,这将是非常不令人满意的(更不用说,编码员的正确痛苦!)

ps如果你发现在我编写它时遵循"未知不能满足约束"这样的逻辑是有挑战性的,那么考虑你可以通过避免SQL DDL中的可为空的列和SQL中的任何内容来省去所有这些产生空值的DML(例如外连接)!

---

@Jamie Ide：实际上，我对此有另一个答案：因为涉及空值的“ NOT IN（子查询）”会产生意想不到的结果，因此很想完全避免使用“ IN（子查询）”，而总是使用“ NOT EXISTS（子查询）”。 （就像我曾经做过的一样！），因为它似乎总是能够正确处理null。但是，在某些情况下，“ NOT IN（子查询）”给出了预期的结果，而“ NOT EXISTS（子查询）”给出了意外的结果！如果我可以找到关于该主题的笔记（因为这不是直觉，所以需要注意），我可能会写这篇文章，但是结论是相同的：避免使用null！

6> Dave Costa..：

---

在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)产生UNKNOWN.总的来说,条件的真实性是未知的,最终基本上被视为假.

7> onedaywhen..：

---

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

考虑一个表sp来表示供应商(sno),他们知道pno在数量(qty)中提供parts ().该表目前包含以下值:

      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.

然后可以使用'减'关系运算符(作为EXCEPT标准SQL中的关键字)获得结果,例如

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;

8> Cruachan..：

---

Null表示和缺少数据,即它是未知的,而不是数据值.编程背景的人很容易混淆这一点,因为在C类语言中使用指针时,null确实没什么.

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

然而,在第二个你可以减少它

选择'true',其中3不在(null)

因此,没有返回任何内容,因为解析器对您要比较它的集合一无所知 - 它不是空集而是未知集.使用(1,2,null)没有帮助,因为(1,2)集显然是错误的,但是那时你正在和那个未知的那个,这是未知的.

9> Mihai..：

---

如果要使用NOT IN过滤包含NULL的子查询,则只检查非空

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