NOT IN 子句中的 NULL 值

Compare to null is undefined，除非使用 IS NULL。

因此，当将 3 与 NULL（查询 A）进行比较时，它返回 undefined。

即 SELECT 'true'，其中 3 in （1,2，null） 和 SELECT 'true' where 3 not in （1,2，null）

将产生相同的结果，因为 NOT （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 ） 产生 UNKNOWN。因此，总体而言，该条件的真实性是未知的，最终基本上被视为 FALSE。

NOT IN与未知值相比返回 0 条记录

由于是未知数，因此在可能值列表中包含 a 或 s 的查询将始终返回记录，因为无法确定该值不是正在测试的值。NULLNOT INNULLNULL0NULL

查询 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

When is on 为 UNKNOWN，因此谓词的计算结果为 UNKNOWN，并且您不会获得任何行。ansi_nulls3 <> null

When is off 为 true，因此谓词的计算结果为 true，并且您得到一行。ansi_nulls3 <> null

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

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

但是，在第二种情况下，您可以将其简化为

选择“true”，其中 3 不在 （null）

因此，不会返回任何内容，因为解析器对要比较它的集合一无所知 - 它不是一个空集，而是一个未知集。使用 （1， 2， null） 无济于事，因为 （1,2） 集显然是错误的，但是你正在对未知数进行攻击，这是未知数。

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

每当使用 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 查询中，而不是 SQL DDL 中。SELECTCONSTRAINT

但是，特别是考虑到标题的措辞，我想指出，这里的一些陈述可能是误导性的陈述，类似于（释义）

当谓词的计算结果为 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 的回答，第一个约束 （using ） 的计算结果为 TRUE，第二个约束 （using ） 的计算结果为 UNKNOWN。但是，插入成功了！因此，在这种情况下，说“你没有得到任何行”是不正确的，因为我们确实因此插入了一行。INNOT IN

就 SQL-92 标准而言，上述效果确实是正确的。比较和对比 SQL-92 规范中的以下部分

7.6 where 子句

的结果是 T 的那些行的表格 其中搜索条件的结果为 true。

4.10 完整性约束

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

换言之：

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

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

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

p.s. 如果您发现像我编写它时那样遵循“未知不会满足约束”这样的逻辑具有挑战性，那么请考虑您只需避免 SQL DDL 中的可为 null 列和 SQL DML 中任何产生 null 的列（例如外部连接）即可免除所有这些！

从这里的答案可以得出结论，不能正确处理空值，应该避免使用。然而，这样的结论可能还为时过早。在以下场景中，归功于 Chris Date（Database Programming and Design，第 2 卷第 9 期，1989 年 9 月），它是正确处理 null 值并返回正确的结果，而不是 .NOT IN (subquery)NOT EXISTSNOT INNOT EXISTS

考虑一个表格来表示已知供应零件 （） 的供应商 （） 的数量 （）。该表当前包含以下值：spsnopnoqty

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

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

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

以下用途仅用于正确标识供应商“S2”：NOT IN

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'
                       );

但是，以下查询使用相同的一般结构，但结果中错误地包含供应商“S1”（即数量为空）：NOT EXISTS

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已知供应已知数量的零件的供应商

请注意，在引用 .spqsp

然后可以使用“减号”关系运算符（标准 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. 涉及产量的比较NULLUNKNOWN
2. 没有一个操作数且至少有一个操作数的表达式产生 （refORTRUEUNKNOWNUNKNOWN)
3. 的产量（参考文献NOTUNKNOWNUNKNOWN)

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