NULL和=NULL有什么区别吗?

I am surprised to see that IS NULL and =NULL are yielding different results in a select query. What is difference between them? When to use what. I would be glad if you can explain me in detail.

我很惊讶地看到，NULL和=NULL在select查询中产生了不同的结果。它们之间有什么区别?什么时候使用什么。如果你能详细地给我解释一下，我会很高兴。

3 个解决方案

#1

= NULL is always unknown (this is piece of 3 state logic), but WHERE clause treats it as false and drops from the result set. So for NULL you should use IS NULL

= NULL总是未知的(这是3个状态逻辑的一部分)，但是WHERE子句将它视为false并从结果集中删除

Reasons are described here: Why does NULL = NULL evaluate to false in SQL server

这里描述了原因:为什么在SQL server中NULL值为NULL值。

#2

To add to existing answers, it depends whether you have ANSI_NULLS on or not, when using "= NULL".

要添加到现有的答案，使用“= NULL”时，取决于是否有ANSI_NULLS。

-- This will print TRUE
SET ANSI_NULLS OFF;
IF NULL = NULL
    PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

-- This will print FALSE
SET ANSI_NULLS ON;
IF NULL = NULL
    PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

#3

because of the three-valued-logic of SQL:

因为SQL的三值逻辑:

http://en.wikipedia.org/wiki/Null_%28SQL%29#Three-valued_logic_.283VL.29

http://en.wikipedia.org/wiki/Null_%28SQL%29 Three-valued_logic_.283VL.29

Extract of a relevant paragraph :

有关段落摘录:

Challenges

挑战

Null has been the focus of controversy and a source of debate because of its associated three-valued logic (3VL), special requirements for its use in SQL joins, and the special handling required by aggregate functions and SQL grouping operators. Computer science professor Ron van der Meyden summarized the various issues as: "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL." Although various proposals have been made for resolving these issues, the complexity of the alternatives has prevented their widespread adoption.

Null因其关联的三值逻辑(3VL)、在SQL连接中使用它的特殊要求以及聚合函数和SQL分组操作符所需的特殊处理而成为争议的焦点和争论的焦点。计算机科学教授Ron van der Meyden将各种问题总结为:“SQL标准中的不一致性意味着不可能将任何直观的逻辑语义归因于SQL中的nulls。”虽然为解决这些问题提出了各种各样的建议，但是各种备选办法的复杂性阻碍了它们的广泛采用。

#1