当我认为两个查询是相同的，一个使用not in约束条件，另一个使用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返回一个结果。
查询 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对比未知值时返回0条记录由于NULL是一个未知值，所以在NOT IN查询中包含了NULL或NULL在可能值的列表中，始终会返回0条记录，因为无法确定NULL值是否为测试的值。
每当你使用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。
与 null 比较会得到未定义的结果，除非你使用 IS NULL。
所以，当比较 3 和 NULL（查询 A）时，它返回未定义。
例如：SELECT 'true' where 3 in (1,2,null) 和 SELECT 'true' where 3 not in (1,2,null) 将产生相同的结果，因为 NOT (UNDEFINED) 仍然是未定义，而不是 TRUE。
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 的比较结果为 UNKNOWNTRUE 且至少有一个操作数为 UNKNOWN 时，OR 表达式的结果为 UNKNOWN（ref）UNKNOWN 的 NOT 结果为 UNKNOWN（ref）你可以将上面的示例扩展到多于两个值（例如 NULL、1 和 2），但结果仍然相同：如果其中一个值为 NULL，则不会匹配任何行。
如果您想使用NOT IN筛选包含NULL的子查询，请检查是否为空
SELECT blah FROM t WHERE blah NOT IN
(SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT NULL )
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;
IN）评估为TRUE，第二个约束（使用NOT IN）评估为UNKNOWN。然而，插入操作成功了！因此，在这种情况下，严格来说不能说“你没有得到任何行”，因为我们确实插入了一行作为结果。NOT IN (subquery) 可能会产生意外结果，所以避免使用 IN (subquery) 并总是使用 NOT EXISTS (subquery)（就像我曾经做过的那样！）似乎总是正确处理空值。然而，有些情况下 NOT IN (subquery) 会给出预期结果，而 NOT EXISTS (subquery) 则会给出意外结果！如果我能找到关于这个主题的笔记（需要笔记，因为它不直观！），我可能会写一篇文章。但结论是相同的：避免使用空值！ - onedaywhenCREATE TABLE T ( a INT NOT NULL UNIQUE, b INT CHECK( a = b ) ); - 这里的意图是 b 要么等于 a，要么为 null。如果约束条件必须为 TRUE 才能满足，则需要显式处理 nulls，例如 CHECK( a = b OR b IS NULL )。因此，每个约束条件都需要用户为涉及到的每个可空列添加 ...OR IS NULL 逻辑：更复杂，当他们忘记这样做时会出现更多错误等。所以我认为 SQL 标准委员会只是试图实用主义。 - onedaywhenb中具有空值：(1)SELECT COUNT(b) FROM T返回大于0的数字。(2)SELECT SUM(b) FROM T返回null。我认为委员会考虑了现有的SQL产品如何处理每种情况以及大多数用户会发现什么更有用，并决定不一致。也许他们是勉强这样做的，但这不是疏忽。还有其他需要学习的“特殊情况”！ - onedaywhen在A中，3与集合的每个成员进行相等性测试，产生 (FALSE, FALSE, TRUE, UNKNOWN)。由于其中一个元素为TRUE，条件为TRUE。(这里也可能发生了一些短路操作，因此它实际上会在第一个TRUE出现时停止并且不会评估3=NULL。)
在B中，我认为它将条件评估为NOT(3在(1,2,null)中)。将3与集合进行相等性测试，得到(FALSE, FALSE, UNKNOWN)，这被聚合为UNKNOWN。NOT(UNKNOWN)得到UNKNOWN。因此，整个条件的真值为未知，在最后被视为FALSE。
NOT IN (subquery)不能正确处理null，应该使用NOT EXISTS。然而，这样的结论可能过早。在以下情况下，由Chris Date（数据库编程和设计，第2卷第9期，1989年9月）提供的资料表明，NOT IN可以正确处理null并返回正确的结果，而不是NOT EXISTS。sp，表示已知以数量qty供应零件pno的供应商sno。该表目前包含以下值： VALUES ('S1', 'P1', NULL),
('S2', 'P1', 200),
('S3', 'P1', 1000)
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'（即其数量为null）：
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;
Null表示数据缺失，即未知，而不是无数据值。对于来自编程背景的人来说，很容易混淆这一点，因为在C类型语言中，当使用指针时，null确实是什么都没有。
因此，在第一个例子中，3确实在(1,2, null)集合中，因此返回true。
然而，在第二个例子中，您可以将其简化为
select 'true' where 3 not in (null)
因此，没有返回任何内容，因为解析器不知道您要与之进行比较的集合 - 它不是空集，而是未知集合。 使用（1、2、null）也没有帮助，因为（1、2）集合显然是false，但是然后你将其与未知and，这是未知的。
NOT IN转换为一系列的<> and会改变“不在这个集合中”的语义行为，变成其他东西了吗？ - Ian Boydnull的含义解释为“某个未知的值”，那么语义就是一致的：3 in (1, 2, 3, unknown)是真的，因为你_知道_3在这个组中。3 not in (1, 2, unknown)既不是真的也不是假的，因为它_可能_在这个组中。“给定1、2和我们不知道实际值的东西，3不在这个集合中吗？”我不确定。 - Hau