SQL Server：为什么比较 null=value 对于 NOT IN 返回 true？

Question

为什么 value 与 null 的比较会返回 false，除非使用 NOT IN 时返回 true？

---

给定一个查询来查找所有拥有帖子的 stackoverflow 用户：

SELECT * FROM Users
WHERE UserID IN (SELECT UserID FROM Posts)

这按预期工作；我得到了所有拥有帖子的用户的列表。

现在查询逆；查找所有没有发布帖子的 stackoverflow 用户：

SELECT * FROM Users
WHERE UserID NOT IN (SELECT UserID FROM Posts)

这不会返回任何记录，这是不正确的。

给定假设数据¹

Users              Posts
================   ===============================
UserID  Username   PostID   UserID  Subject
------  --------   -------  ------  ----------------
1       atkins     1        1       Welcome to stack ov...
2       joels      2        2       Welcome all!
...     ...        ...      ...
399573  gt6989b    ...      ...
...     ...        ...      ...
                   10592    null    (deleted by nsl&fbi...
                   ...      ... 

并假设 NULL 规则：

• NULL = NULL 计算结果为未知
• NULL <> NULL 计算结果为未知
• value = NULL 计算结果为未知

如果我们查看第二个查询，我们感兴趣的是查找 Users.UserID 不 的所有行在 Posts.UserID 列中找到。我将按如下逻辑进行：

检查 UserID 1

• 1 = 1 返回 true。因此我们得出结论，该用户有一些帖子，并且不将它们包含在输出列表中

现在检查 UserID 2：

• 2 = 1 返回 false，因此我们继续查找
• 2 = 2 返回 true，因此我们得出结论该用户有一些帖子，并且不将其包含在输出列表中

现在检查 UserID 399573

• 399573 = 1 返回 false，因此我们继续查找
• 399573 = 2 返回 false，所以我们继续寻找
• ...
• 399573 = null 返回未知，所以我们继续寻找
• ...

我们没有找到 UserID 399573 的帖子，因此我们将把他包含在输出中列表。

但 SQL Server 不这样做。如果您的 in 列表中有 NULL，那么它会突然找到匹配项。 它突然找到了一个匹配项。突然 399573 = null 的计算结果为 true。

为什么value 与null 的比较返回unknown，除非它返回true？

编辑：我知道我可以通过专门排除空值来解决这种无意义行为：

SELECT * FROM Users
WHERE UserID NOT IN (
   SELECT UserID FROM Posts
   WHERE UserID IS NOT NULL)

但我不应该这样做，据我所知，布尔逻辑应该是没有它就很好 - 因此我的问题。

脚注

• ¹假设数据；如果你不喜欢它：弥补你的不足。
• celko 现在有了自己的标签

Answer 1

常见问题，预设答案：

NOT IN 子句的行为可能会令人困惑，因此需要一些解释。考虑以下查询：

SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)

尽管 AdventureWorks.Person.Contact 中有一千多个不同的姓氏，但该查询不返回任何内容。对于初学者数据库程序员来说，这可能看起来违反直觉，但实际上是完全有道理的。该解释由几个简单的步骤组成。首先，考虑以下两个查询，它们显然是等效的：

SELECT LastName, FirstName FROM Person.Contact

WHERE LastName IN('Hedlund', 'Holloway', NULL)



SELECT LastName, FirstName FROM Person.Contact

WHERE LastName='Hedlund' OR LastName='Holloway' OR LastName=NULL

请注意，两个查询都返回预期结果。现在，让我们回顾一下德摩根定理，该定理指出：

not (P and Q) = (not P) or (not Q)

not (P or Q) = (not P) and (not Q)

我正在从维基百科（http://en.wikipedia.org/wiki/De_Morgan_duality）剪切和粘贴。将 DeMorgan 定理应用于此查询，可以得出这两个查询也是等价的：

SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)



SELECT LastName, FirstName FROM Person.Contact

WHERE LastName<>'Hedlund' AND LastName<>'Holloway' AND LastName<>NULL

最后一个 LastName<>NULL 永远不可能为 true

Answer 2

你第一句话中的假设是不正确的：

为什么比较值
null 返回 false，除非使用
NOT IN，哪里返回true？

但是将值与 null 进行比较不会返回 false；它返回未知。 unknown 有其自己的逻辑：

unknown  AND  true   = unknown
unknown  OR   true   = true
unknown  OR   false  = unknown

这是如何实现的一个示例：

where 1 not in (2, null)
--> where 1 <> 2 and 1 <> null
--> where true and unknown
--> where unknown

where 子句仅匹配 true，因此这会过滤掉任何行。

您可以在 Wikipedia 中找到 3 值逻辑的全部优点。