SQL Server:为什么比较null = value的NOT IN返回true? [英] SQL Server: Why does comparison null=value return true for NOT IN?

问题描述

为什么value与null的比较返回false(除了使用NOT IN时返回true)?

Why does the comparison of value to null return false, except when using a NOT IN, where it returns true?

给出查询以查找拥有帖子的所有stackoverflow用户:

Given a query to find all stackoverflow users who have a post:

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

这按预期工作；我得到了所有发布信息的用户的列表.

This works as expected; i get a list of all users who have a post.

现在查询反函数；查找所有没有发帖的stackoverflow用户:

Now query for the inverse; find all stackoverflow users who don't have a post:

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

这将不返回任何记录，这是不正确的.

This returns no records, which is incorrect.

给出假设数据 ¹

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规则:

And assume the rules of NULLs:

• NULL = NULL评估为未知
• NULL <> NULL评估为未知
• value = NULL评估未知

• NULL = NULL evaluates to unknown
• NULL <> NULL evaluates to unknown
• value = NULL evaluates unknown

如果我们查看第二个查询，则有兴趣查找在Posts.UserID列中找到 not 的Users.UserID为 not 的所有行.我将在逻辑上进行如下操作:

If we look at the 2nd query, we're interested in finding all rows where the Users.UserID is not found in the Posts.UserID column. i would proceed logically as follows:

检查用户ID 1

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

• 1 = 1 returns true. So we conclude that this user has some posts, and do not include them in the output list

现在检查用户ID 2:

Now check UserID 2:

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

• 2 = 1 returns false, so we keep looking
• 2 = 2 returns true, so we conclude that this user has some posts, and do not include them in the output list

现在检查用户ID 399573

Now check UserID 399573

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

• 399573 = 1 returns false, so we keep looking
• 399573 = 2 returns false, so we keep looking
• ...
• 399573 = null returns unknown, so we keep looking
• ...

我们没有发现UserID 399573的帖子，因此我们将其包括在输出列表中.

We found no posts by UserID 399573, so we would include him in the output list.

除了SQL Server不会执行此操作.如果您在in列表中有一个NULL，那么突然它会找到一个匹配项. 突然发现一个匹配项.突然399573 = null计算结果为true.

Except SQL Server doesn't do this. If you have a NULL in your in list, then suddenly it finds a match. It suddenly finds a match. Suddenly 399573 = null evaluates to true.

为什么value与null的比较返回未知，除非返回true?

Why does the comparison of value to null return unknown, except when it returns true?

编辑:我知道我可以通过专门排除空值来解决这种荒谬行为:

Edit: i know that i can workaround this nonsensical behavior by specifically excluding the nulls:

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

但是我不必说，没有布尔逻辑就可以了-因此是我的问题.

But i shouldn't have to, as far as i can tell the boolean logic should be fine without it - hence my question.

• ¹ 假设数据；如果您不喜欢，请弥补.
• celko现在有了自己的标签

• ¹ hypothetical data; if you don't like it: make up your down.
• celko now has his own tag

推荐答案

常见问题，罐头答案:

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

The behavior of NOT IN clause may be confusing and as such it needs some explanations. Consider the following query:

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

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

Although there are more than a thousand distinct last names in AdventureWorks.Person.Contact, the query returns nothing. This may look counterintuitive to a beginner database programmer, but it actually makes perfect sense. The explanation consist of several simple steps. First of all, consider the following two queries, which are clearly equivalent:

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

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

Note that both queries return expected results. Now, let us recall DeMorgan's theorem, which states that:

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

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

我正在从Wikipedia(http://en.wikipedia.org/wiki/De_Morgan_duality)进行粘贴和粘贴.将DeMorgan定理应用于此查询，可以得出以下两个查询也是等效的:

I am cutting and pasting from Wikipedia (http://en.wikipedia.org/wiki/De_Morgan_duality). Applying DeMorgan's theorem to this queries, it follows that these two queries are also equivalent:

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永远不会为真

This last LastName<>NULL can never be true

这篇关于SQL Server:为什么比较null = value的NOT IN返回true?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！