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，则没有行将匹配。

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(子查询)不能正确处理空值，应该避免使用NOT EXISTS。然而，这样的结论可能为时过早。在下面的场景中，归功于Chris Date(数据库编程与设计，Vol 2 No 9, 1989年9月)，正确处理null并返回正确结果的不是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;

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

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

在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)产生未知。所以总的来说，情况的真相是未知的，最终基本上被视为错误。