当我得到不同的记录计数时，这个问题出现了，我认为是相同的查询，一个使用not in where约束，另一个使用左连接。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返回一个结果。
当前回答
此外，这可能有助于了解join、exists和in之间的逻辑差异 http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx
其他回答
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，则没有行将匹配。
在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)产生未知。所以总的来说，情况的真相是未知的，最终基本上被视为错误。
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
此外，这可能有助于了解join、exists和in之间的逻辑差异 http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx
当与未知值比较时，NOT IN返回0条记录
由于NULL是一个未知值，在可能值列表中包含NULL或NULLs的NOT IN查询将总是返回0记录，因为没有办法确保NULL值不是正在测试的值。
