NOT IN 子句中的 NULL 值

Question

当我得到不同的记录计数时，我认为相同的查询（一个使用 not in where 约束，另一个使用 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 返回结果。

Answer 1

查询 A 与以下内容相同：

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

由于 3 = 3 为 true，因此您会得到结果。

查询 B 与以下内容相同：

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

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

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

Answer 2

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

由于 NULL 是未知值，因此包含 NULL 的 NOT IN 查询可能值列表中的 code> 或 NULL 将始终返回 0 记录，因为无法确保 NULL 值是不是正在测试的值。

Answer 3

每当您使用 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 相同（错误）。
事实并非如此。

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

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

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

Answer 4

与 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) 仍然未定义，但不是 TRUE

Answer 5

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. OR 表达式其中没有一个操作数为 TRUE 并且至少有一个操作数为 UNKNOWN 则产生 UNKNOWN (ref)
3. UNKNOWN 的 NOT 产生 未知（参考）

您可以将上面的示例扩展到两个以上的值（例如 NULL、1 和 2），但结果将是相同的：如果其中一个值是 NULL，则没有行匹配。

Answer 6

如果您想使用 NOT IN 过滤包含 NULL 的子查询，只需检查是否为 null

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

Answer 7

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

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 完整性约束

当且仅当指定的
对于表格的任何行，搜索条件都不为假。

换句话说：

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

在 SQL DDL（即约束）中，当行的计算结果为 UNKNOWN 时，它们不会从结果中删除，因为它确实满足“不为假”条件。

尽管 SQL DML 和 SQL DDL 中的效果分别看起来可能是矛盾的，但通过允许 UNKNOWN 结果满足约束（更准确地说，允许它们不会无法满足约束）来给予 UNKNOWN 结果“无罪”是有实际原因的。 ：如果没有这种行为，每个约束都必须显式处理空值，从语言设计的角度来看，这将非常不令人满意（更不用说，这对编码人员来说是一种痛苦！）

ps如果您发现遵循这样的逻辑具有挑战性：“正如我要写的那样，“未知不会无法满足约束”，然后考虑您可以通过避免 SQL DDL 中的可空列以及 SQL DML 中产生空值的任何内容（例如外连接）来省去所有这些！

Answer 8

在 A 中，测试 3 与集合中每个成员的相等性，产生（FALSE、FALSE、TRUE、UNKNOWN）。 由于元素之一为 TRUE，因此条件为 TRUE。 （也有可能这里发生了一些短路，所以它实际上在遇到第一个 TRUE 时就停止了，并且从不计算 3=NULL。）

在 B 中，我认为它正在将条件评估为 NOT (3 in (1 ,2,空))。 测试 3 是否与集合产生相等（FALSE、FALSE、UNKNOWN），其聚合为 UNKNOWN。 NOT ( UNKNOWN ) 产生 UNKNOWN。 因此，总体而言，情况的真实性是未知的，最终基本上被视为错误。

Answer 9

Null 表示数据不存在，也就是说它是未知的，而不是没有任何数据值。 对于有编程背景的人来说很容易混淆这一点，因为在 C 类型语言中使用指针时 null 确实没什么。

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

在第二种情况下，您可以将其减少为

select 'true' 其中 3 不在 (null) 中

因此不会返回任何内容，因为解析器对您要与之比较的集合一无所知 - 它不是一个空集合，而是一个未知集合。 使用 (1, 2, null) 没有帮助，因为 (1,2) 集显然是假的，但随后你将其与未知相比较，这是未知的。

Answer 10

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

考虑一个表 sp 来表示已知供应零件 (pno) 数量 (qtysno) >）。 该表当前包含以下值：

      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 的键约束。

然后可以使用“减”关系运算符（即标准 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;

Answer 11

这是给男孩的：

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

无论 ansi 设置如何，这都有效

Answer 12

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