T-SQL：什么是NOT（1 = NULL）？

I don't get the simple boolean algebra on my sql-server. According to msdn, the following statement should return "1", but on my server it returns "0". Can you help me?

我的sql-server上没有简单的布尔代数。根据msdn，以下语句应返回“1”，但在我的服务器上它返回“0”。你可以帮我吗？

SET ANSI_NULLS ON
SELECT CASE WHEN NOT(1=NULL) THEN 1 ELSE 0 END

Please have a look at msdn. There it clearly states: "Comparing NULL to a non-NULL value always results in FALSE." - no matter what the ANSI_NULLS-setting is. Thus "1=NULL" should be FALSE and NOT(FALSE) should thus be TRUE and the statement should return "1".

请看看msdn。它明确指出：“将NULL与非NULL值进行比较总是导致FALSE。” - 无论ANSI_NULLS设置是什么。因此，“1 = NULL”应为FALSE，因此NOT（FALSE）应为TRUE，语句应返回“1”。

But on my machine, it returns "0"!

但在我的机器上，它返回“0”！

One explanation might be, that "1=NULL" evaluates to "UNKNOWN". NOT(UNKNOWN) is still UNKNOWN (msdn), which would force the CASE-Statement into the ELSE.

一种解释可能是，“1 = NULL”评估为“UNKNOWN”。 NOT（UNKNOWN）仍然是UNKNOWN（msdn），它会强制CASE语句进入ELSE。

But then the official documentation of the equals-operator would be wrong. I cannot believe this!

但是，等于运营商的官方文件是错误的。我无法相信这！

Can anybody explain this behaviour?

任何人都可以解释这种行为吗？

Thank you very much for any help!

非常感谢您的帮助！

Edit (2012-03-15):

编辑（2012-03-15）：

One thing I just found that might be of interest for some of you:

我发现的一件事可能对你们有些人感兴趣：

CREATE TABLE #FooTest (Value INT)
ALTER TABLE #FooTest WITH CHECK ADD CONSTRAINT ccFooTestValue CHECK (Value>1)
PRINT '(NULL>1) = ' + CASE WHEN NULL>1 THEN 'True' ELSE 'False' END
INSERT INTO #FooTest (Value) VALUES (NULL)

The print-Statement writes 'False', but the insertion runs without error. SQL-Server seems to negate the check-constraint in order to search for rows that do not fulfill the constraint-check:

print-Statement写入'False'，但插入运行没有错误。 SQL-Server似乎否定了检查约束，以便搜索不符合约束检查的行：

IF EXISTS (SELECT * FROM inserted WHERE NOT(Value>NULL)) <Generate error>

Since the check-constraint evaluates to UNKNOWN, the negation is also UNKNOWN and SqlServer does not find any row violating the check-constraint.

由于check-constraint的计算结果为UNKNOWN，因此否定也是UNKNOWN，并且SqlServer没有找到任何违反check-constraint的行。

7 个解决方案

#1

The MSDN page for Equals that you link to definitely appears incorrect.

您链接到的Equals的MSDN页面肯定显示不正确。

Check the MSDN page for SET ANSI_NULLS.

检查SET ANSI_NULLS的MSDN页面。

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN.

当SET ANSI_NULLS为ON时，对空值的所有比较都计算为UNKNOWN。

To get that example SQL statement to work as expected, you should use compare using "IS NULL" or "IS NOT NULL" instead of using the equals operator (=). For example:

要使该示例SQL语句按预期工作，您应该使用“IS NULL”或“IS NOT NULL”而不是使用equals运算符（=）进行比较。例如：

SELECT CASE WHEN NOT(1 IS NULL) THEN 1 ELSE 0 END

SELECT CASE WHEN NOT（1 IS NULL），然后1 ELSE 0 END

要么

SELECT CASE WHEN (1 IS NOT NULL) THEN 1 ELSE 0 END

SELECT CASE WHEN（1 IS NOT NULL）然后1 ELSE 0 END

#2

Yes that link is wrong. File a documentation bug on Microsoft Connect.

是的，链接是错误的。在Microsoft Connect上提交文档错误。

Sql uses three valued logic not boolean logic. true, false, and unknown

Sql使用三值逻辑而不是布尔逻辑。真实，虚假和未知

Most comparison operators (i.e. excluding IS [NOT] NULL) involving NULL result in unknown not True or False. Negating unknown yields unknown as per the truth tables shown here.

涉及NULL的大多数比较运算符（即排除IS [NOT] NULL）导致未知的非True或False。根据此处显示的真值表，否定未知的未知产量。

#3

You want to read the documentation on ANSI_NULLS. SQL actually implements a ternary logic, not boolean logic, where a comparison operation can result in true, false or undefined. Basically, this means that the explanation you proffered is correct.

您想阅读有关ANSI_NULLS的文档。 SQL实际上实现了三元逻辑，而不是布尔逻辑，其中比较操作可能导致true，false或undefined。基本上，这意味着您提供的解释是正确的。

This can be demonstrated with the following query:

这可以通过以下查询来演示：

SET ANSI_NULLS ON
SELECT CASE
  WHEN (1=NULL) THEN 0
  WHEN NOT(1=NULL) THEN 1    
  ELSE -1
END

Which results in -1 on my machine (SQL Server 2005 Enterprise). Changing the first line to SET ANSI_NULLS OFF produces 1 as expected.

这导致我的机器上的-1（SQL Server 2005 Enterprise）。将第一行更改为SET ANSI_NULLS OFF会按预期生成1。

So, is the official documentation wrong? I'd submit that is somewhat misleading. It says that it results in FALSE. Obviously this is wrong. What the documentation meant to say is that comparing a non-null to NULL always results in a mismatch whose value also depends on ANSI_NULLS.

那么，官方文档是错误的吗？我认为这有点误导。它说它导致FALSE。显然这是错误的。文档的意思是将非null与NULL进行比较总是会导致不匹配，其值也取决于ANSI_NULLS。

Of course, on SQL Server 2012, the ANSI_NULLS setting has been removed, and therefore setting it any which way will not change the result.

当然，在SQL Server 2012上，ANSI_NULLS设置已被删除，因此将其设置为不会改变结果的方式。

#4

It's not boolean logic, its trinary logic: {True, False, I Don't Know.} Break it down this way:

它不是布尔逻辑，它的三元逻辑：{真，假，我不知道。}以这种方式分解：

IF 1=NULL
    print 'True'
else
    print 'False'

Generates False because 1=NULL equals NULL, aka "not True"

生成False，因为1 = NULL等于NULL，又名“not True”

IF not(1=NULL)
    print 'True'
else
    print 'False'

Also generates False because not(1=NULL) equals not(NULL) equals NULL, aka "not True". This gets you to

也生成False，因为not（1 = NULL）等于not（NULL）等于NULL，又名“not True”。这会让你

SELECT CASE WHEN NOT(1=NULL) THEN 1 ELSE 0 END

which as per above is the same as

如上所述与...相同

SELECT CASE WHEN NULL THEN 1 ELSE 0 END

which, since NULL is not true, resovles to the ELSE clause.

由于NULL不为真，因此转而使用ELSE子句。

In short, as far as I'm concerned the documentation is incorrect. Distressing, but not unique, and so not entirely surprising.

简而言之，就我而言，文档是不正确的。令人痛苦，但不是唯一的，所以并不完全令人惊讶。

#5

Try using EXISTS in a subquery, it uses 2 valued logic and will give you the true/false you are looking for.

尝试在子查询中使用EXISTS，它使用2值逻辑，并为您提供正在寻找的真/假。

#6

From BOL (credit to Thomas):

从BOL（信贷到托马斯）：

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

仅当比较的一个操作数是NULL或文字NULL的变量时，SET ANSI_NULLS ON才会影响比较。如果比较的两侧都是列或复合表达式，则该设置不会影响比较。

So I guess the NOT operation is checking 1=NULL which is unknown and because this is not a variable or literal NULL gets the ELSE part of your comparison as you hypothesised.

所以我猜NOT操作是检查1 = NULL，这是未知的，因为这不是变量或文字NULL在您假设的情况下获得比较的ELSE部分。

#7

1=NULL seems to return FALSE only when ANSI_NULLS is OFF. Otherwise it's indeterminate. The msdn page probably needs to be edited to clarify that point.

1 = NULL仅在ANSI_NULLS为OFF时返回FALSE。否则它是不确定的。可能需要编辑msdn页面以澄清这一点。

SET ANSI_NULLS OFF
SELECT CASE WHEN (1=NULL) THEN 'true' ELSE 'false or unknown' END --returns false or unknown
, CASE WHEN NOT(1=NULL) THEN 'true' ELSE 'false or unknown' END --returns true
go

SET ANSI_NULLS ON
SELECT CASE WHEN (1=NULL) THEN 'true' ELSE 'false or unknown' END --returns false or unknown
, CASE WHEN NOT(1=NULL) THEN 'true' ELSE 'false or unknown' END --returns false or unknown
go

#1