为什么 NULL = NULL 在 SQL 服务器中评估为假

Why does NULL = NULL evaluate to false in SQL server

提问by Byron Whitlock

In SQL server if you have nullParam=NULLin a where clause, it always evaluates to false. This is counterintuitive and has caused me many errors. I do understand the IS NULLand IS NOT NULLkeywords are the correct way to do it. But why does SQL server behave this way?

在 SQL Server 中，如果你有 nullParam=NULLwhere 子句，它总是评估为假。这是违反直觉的，并给我带来了很多错误。我确实理解IS NULL和IS NOT NULL关键字是正确的方法。但是为什么 SQL Server 会这样呢？

回答by Scott Ivey

Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.

在这种情况下，将 null 视为“未知”（或“不存在”）。在这两种情况下，您都不能说它们相等，因为您不知道它们中的任何一个的价值。因此， null=null 评估为不为真（假或空，取决于您的系统），因为您不知道表示它们相等的值。此行为在 ANSI SQL-92 标准中定义。

EDIT: This depends on your ansi_nullssetting. if you have ANSI_NULLS off, this WILL evaluate to true. Run the following code for an example...

编辑：这取决于您的ansi_nulls设置。如果您关闭了 ANSI_NULLS，这将评估为真。运行以下代码作为示例...

set ansi_nulls off

if null = null
    print 'true'
else
    print 'false'


set ansi_nulls ON

if null = null
    print 'true'
else
    print 'false'

回答by Neil McGuigan

How old is Frank? I don't know (null).

弗兰克几岁了？我不知道（空）。

How old is Shirley? I don't know (null).

雪莉几岁了？我不知道（空）。

Are Frank and Shirley the same age?

弗兰克和雪莉是同龄人吗？

Correct answer should be "I don't know" (null), not "no", as Frank and Shirley mightbe the same age, we simply don't know.

正确答案应该是“我不知道”（空），而不是“不”，因为弗兰克和雪莉可能是同龄人，我们根本不知道。

回答by MaD70

Here I will hopefully clarify my position.

在这里，我希望能澄清我的立场。

That NULL = NULLevaluate to FALSEis wrong. Hacker and Mister correctly answered NULL. Here is why. Dewayne Christensen wrote to me, in a comment to Scott Ivey:

那个NULL = NULL评估FALSE是错误的。黑客和先生正确回答NULL。这是为什么。Dewayne Christensen 在给Scott Ivey的评论中写信给我：

Since it's December, let's use a seasonal example. I have two presents under the tree. Now, you tell me if I got two of the same thing or not.

由于是 12 月，让我们使用季节性示例。我在树下有两个礼物。现在，你告诉我是否有两个相同的东西。

They can be different or they can be equal, you don't knowuntil one open both presents. Who knows? You invited two people that don't know each other and both have done to you the same gift - rare, but not impossible §.

它们可以不同，也可以相同，直到打开两个礼物你才知道。谁知道？你邀请了两个互不认识的人，他们都给了你同样的礼物——罕见，但并非不可能§。

So the question: are these two UNKNOWN presents the same (equal, =)? The correct answer is: UNKNOWN (i.e. NULL).

所以问题是：这两个 UNKNOWN 呈现是否相同（等于，=）？正确答案是：未知（即NULL）。

This example was intended to demonstratethat "..(falseor null, depending on your system).." is a correct answer - it is not, onlyNULLis correct in 3VL (or is ok for you to accept a system which gives wrong answers?)

这个例子是为了证明“..（false或null，取决于你的系统......”是一个正确的答案——它不是，只有NULL在 3VL 中才是正确的（或者你可以接受一个给出错误答案的系统？ )

A correct answer to this questionmust emphasize this two points:

这个问题的正确答案必须强调以下两点：

• three-valued logic (3VL) is counterintuitive (see countless other questions on this subject on Stackoverflow and in other forum to make sure);
• SQL-based DBMSes often do not respect even 3VL, they give wrong answers sometimes (as, the original poster assert, SQL Server do in this case).

• 三值逻辑 (3VL) 是违反直觉的（请参阅 Stackoverflow 和其他论坛上有关此主题的无数其他问题以确保）；
• 基于 SQL 的 DBMS 通常甚至不尊重 3VL，它们有时会给出错误的答案（正如原始海报断言，SQL Server 在这种情况下所做的那样）。

So I reiterate: SQL does not any good forcing one to interpret the reflexive property of equality, which state that:

所以我重申：SQL 没有任何好处强迫人们解释平等的自反属性，它指出：

for any x, x = x§§(in plain English: whatever the universe of discourse, a "thing" is always equal to itself).

for any x, x = x§§（通俗易懂的英语：无论讨论的领域如何，“事物”总是等于自身）。

.. in a 3VL (TRUE, FALSE, NULL). The expectation of people would conform to 2VL (TRUE, FALSE, which even in SQL is valid for all other values), i.e. x = xalways evaluate toTRUE, for any possible value of x - no exceptions.

.. 在 3VL ( TRUE, FALSE, NULL) 中。人们的期望将符合 2VL（TRUE, FALSE，即使在 SQL 中也适用于所有其他值），即对于 x 的任何可能值，x = x始终评估为TRUE，没有例外。

Note also that NULLs are valid " non-values" (as their apologists pretend them to be) which one can assign as attribute values(??) as part of relation variables. So they are acceptable values of every type (domain), not only of the type of logical expressions.

另请注意，NULL 是有效的“非值”（正如他们的辩护者假装的那样），可以将其分配为属性值（??）作为关系变量的一部分。所以它们是每种类型（域）的可接受值，而不仅仅是逻辑表达式的类型。

And this was my point: NULL, as value, is a "strange beast". Without euphemism, I prefer to say: nonsense.

而这是我的观点：NULL作为价值，是一个“奇怪的野兽”。没有委婉的说法，我宁愿说：废话。

I think that this formulation is much more clear and less debatable - sorry for my poor English proficiency.

我认为这个表述更清晰，更没有争议——对不起，我的英语水平很差。

This is only oneof the problems of NULLs. Better to avoid them entirely, when possible.

这仅仅是一个的空值的问题。如果可能，最好完全避免它们。

§we are concerned about valueshere, so the fact that the two presents are alwaystwo different physical objects are not a valid objection; if you are not convinced I'm sorry, it is not this the place to explain the difference between value and "object" semantics (Relational Algebra has value semantics from the start - see Codd's information principle; I think that some SQL DBMS implementors don't even care about a common semantics).

§我们在这里关心的是价值，所以两个礼物总是两个不同的物理对象的事实不是有效的反对；如果您不相信我很抱歉，这不是解释值语义和“对象”语义之间区别的地方（关系代数从一开始就具有值语义 - 请参阅 Codd 的信息原则；我认为某些 SQL DBMS 实现者不甚至不关心共同的语义）。

§§to my knowledge, this is an axiom accepted (in a form or another, but always interpreted in a 2VL) since antiquity and that exactlybecause is so intuitive. 3VLs (is a family of logics in reality) is a much more recent development (but I'm not sure when was first developed).

§§ 据我所知，这是一个自古以来就被接受的公理（以某种形式或另一种形式，但总是以 2VL 解释），这正是因为如此直观。3VL（实际上是一系列逻辑）是最近的发展（但我不确定最早是何时开发的）。

Side note:if someone will introduce Bottom, Unitand OptionTypes as attempts to justify SQL NULLs, I will be convinced only after a quite detailed examination that will shows of how SQL implementations with NULLs have a sound type system and will clarify, finally, what NULLs (these "values-not-quite-values") really are.

旁注：如果有人会引入Bottom、Unit和OptionTypes 来试图证明 SQL NULL 的合理性，那么只有在进行了非常详细的检查后，我才会相信，该检查将显示带有 NULL 的 SQL 实现如何具有健全的类型系统，并最终澄清，什么是 NULL（这些“值不完全值”）到底是什么。

---

---

In what follow I will quote some authors. Any error or omission is probably mine and not of the original authors.

下面我将引用一些作者的话。任何错误或遗漏都可能是我的，而不是原作者的。

Joe Celko on SQL NULLs

Joe Celko 关于 SQL NULL

I see Joe Celko often cited on this forum. Apparently he is a much respected author here. So, I said to myself: "what does he wrote about SQL NULLs? How does he explain NULLs numerous problems?". One of my friend has an ebook version of Joe Celko's SQL for smarties: advanced SQL programming, 3rd edition. Let's see.

我看到 Joe Celko 经常在这个论坛上被引用。显然，他在这里是一位备受尊敬的作家。所以，我对自己说：“他写了什么关于 SQL NULL 的内容？他如何解释 NULL 的众多问题？”。我的一位朋友有一本Joe Celko 为聪明人编写的 SQL的电子书版本：高级 SQL 编程，第 3 版。让我们来看看。

First, the table of contents. The thing that strikes me most is the number of times that NULL is mentioned and in the most varied contexts:

首先是目录。最让我印象深刻的是 NULL 被提及的次数以及在最多样化的上下文中：

3.4 Arithmetic and NULLs 109
3.5 Converting Values to and from NULL 110
3.5.1 NULLIF() Function 110
6 NULLs: Missing Data in SQL 185
6.4 Comparing NULLs 190
6.5 NULLs and Logic 190
6.5.1 NULLS in Subquery Predicates 191
6.5.2 Standard SQL Solutions 193
6.6 Math and NULLs 193
6.7 Functions and NULLs 193
6.8 NULLs and Host Languages 194
6.9 Design Advice for NULLs 195
6.9.1 Avoiding NULLs from the Host Programs 197
6.10 A Note on Multiple NULL Values 198
10.1 IS NULL Predicate 241
10.1.1 Sources of NULLs 242
...

3.4算术和空值109
3.5转换数值和从NULL 110
3.5.1 NULLIF（）函数110
6个空值：在SQL缺失数据185米
6.4比较的NULL 190个
6.5空值和逻辑190
在子查询6.5.1 NULLS谓词191
6.5.2标准SQL解决方案193
6.6数学和NULL值193个
6.7功能和NULL值193
6.8空值和房东语言194
6.9 NULL值195个设计建议
6.9.1从主机程序197避免空值
在多个NULL值198 6.10注
10.1 IS NULL谓词241
10.1。 1 NULL 的来源 242
...

and so on. It rings "nasty special case" to me.

等等。它对我来说是“令人讨厌的特殊情况”。

I will go into some of these cases with excerpts from this book, trying to limit myself to the essential, for copyright reasons. I think these quotes fall within "fair use" doctrine and they can even stimulate to buy the book - so I hope that no one will complain (otherwise I will need to delete most of it, if not all). Furthermore, I shall refrain from reporting code snippets for the same reason. Sorry about that. Buy the book to read about datailed reasoning.

出于版权原因，我将结合本书的摘录来探讨其中的一些案例，试图将自己限制在必不可少的部分。我认为这些引用属于“合理使用”原则，它们甚至可以刺激人们购买这本书——所以我希望没有人会抱怨（否则我将需要删除大部分，如果不是全部）。此外，出于同样的原因，我将避免报告代码片段。对于那个很抱歉。购买这本书以了解数据推理。

Page numbers between parenthesis in what follow.

后面括号内的页码。

NOT NULL Constraint (11)

The most important column constraint is the NOT NULL, which forbids the use of NULLs in a column. Use this constraint routinely, and remove it only when you have good reason. It will help you avoid the complications of NULL valueswhen you make queries against the data.

It is not a value; it is a marker that holds a place where a value might go.

非空约束 (11)

最重要的列约束是 NOT NULL，它禁止在列中使用 NULL。定期使用此约束，并仅在您有充分理由时将其删除。当您对数据进行查询时，它将帮助您避免NULL 值的复杂性。

它不是一个值；它是一个标记，包含一个值可能去的地方。

Again this "value but not quite a value" nonsense. The rest seems quite sensible to me.

再次这个“有价值但不是很有价值”的废话。其余的对我来说似乎很明智。

(12)

In short, NULLs cause a lot of irregular features in SQL, which we will discuss later. Your best bet is just to memorize the situations and the rules for NULLs when you cannot avoid them.

(12)

简而言之，NULL 会导致 SQL 中的许多不规则特性，我们将在后面讨论。最好的办法就是记住无法避免的情况和 NULL 的规则。

Apropos of SQL, NULLs and infinite:

关于 SQL、NULL 和无穷大：

(104) CHAPTER 3: NUMERIC DATA IN SQL

SQL has not accepted the IEEE model for mathematics for several reasons.

...

If the IEEE rules for math were allowed in SQL, then we would need type conversion rules for infinite and a way to represent an infinite exact numeric value after the conversion. People have enough trouble with NULLs, so let's not go there.

(104) 第 3 章：SQL 中的数字数据

出于多种原因，SQL 尚未接受 IEEE 数学模型。

...

如果在 SQL 中允许 IEEE 数学规则，那么我们将需要无限的类型转换规则以及在转换后表示无限精确数值的方法。人们对 NULL 有足够的麻烦，所以我们不要去那里。

SQL implementations undecided on what NULL really means in particular contexts:

SQL 实现未决定 NULL 在特定上下文中的真正含义：

3.6.2 Exponential Functions (116)

The problem is that logarithms are undefined when (x <= 0). Some SQL implementationsreturn an error message, some return a NULLand DB2/ 400; version 3 release 1 returned *NEGINF (short for “negative infinity”) as its result.

3.6.2 指数函数 (116)

问题是当 (x <= 0) 时，对数是未定义的。一些 SQL 实现返回错误消息，一些返回NULL和 DB2/400；第 3 版第 1 版返回 *NEGINF（“负无穷大”的缩写）作为其结果。

Joe Celko quoting David McGoveran and C. J. Date:

Joe Celko 引用 David McGoveran 和 CJ Date：

6 NULLs: Missing Data in SQL (185)

In their book A Guide to Sybase and SQL Server, David McGoveran and C. J. Date said: “It is this writer's opinion than NULLs, at least as currently defined and implemented in SQL, are far more trouble than they are worth and should be avoided; they display very strange and inconsistent behavior and can be a rich source of error and confusion. (Please note that these comments and criticisms apply to any system that supports SQL-style NULLs, not just to SQL Server specifically.)”

6 NULL：SQL 中的缺失数据 (185)

在他们的《Sybase 和 SQL Server 指南》一书中，David McGoveran 和 CJ Date 说：“这是作者的观点，至少在目前在 SQL 中定义和实现的 NULL 比 NULL 麻烦得多，应该避免；它们表现出非常奇怪和不一致的行为，可能是错误和混乱的丰富来源。（请注意，这些评论和批评适用于任何支持 SQL 样式 NULL 的系统，而不仅仅是 SQL Server。）”

NULLs as a drug addiction:

NULL 作为毒瘾：

(186/187)

In the rest of this book, I will be urging you not to use them, which may seem contradictory, but it is not. Think of a NULL as a drug; use it properly and it works for you, but abuse it and it can ruin everything. Your best policy is to avoid NULLs when you can and use them properly when you have to.

(186/187)

在本书的其余部分，我将敦促您不要使用它们，这可能看起来自相矛盾，但事实并非如此。将 NULL 视为一种药物；正确使用它，它对你有用，但滥用它，它会毁了一切。最好的策略是尽可能避免 NULL，并在必要时正确使用它们。

My unique objection here is to "use them properly", which interacts badly with specific implementation behaviors.

我在这里唯一的反对意见是“正确使用它们”，这与特定的实现行为相互作用很差。

6.5.1 NULLS in Subquery Predicates (191/192)

People forget that a subquery often hides a comparison with a NULL. Consider these two tables:

...

The result will be empty. This is counterintuitive, but correct.

6.5.1 子查询谓词中的 NULLS (191/192)

人们忘记了子查询经常隐藏与 NULL 的比较。考虑这两个表：

...

结果将是空的。这是违反直觉的，但正确的。

(separator)

（分隔器）

6.5.2 Standard SQL Solutions (193)

SQL-92 solved some of the 3VL (three-valued logic) problems by adding a new predicate of the form:

<search condition> IS [NOT] TRUE | FALSE | UNKNOWN

6.5.2 标准 SQL 解决方案 (193)

SQL-92 通过添加以下形式的新谓词解决了一些 3VL（三值逻辑）问题：

<搜索条件> IS [NOT] TRUE | 错误 | 未知

But UNKNOWN is a source of problems in itself, so that C. J. Date, in his book cited below, reccomends in chapter 4.5. Avoiding Nulls in SQL:

但是 UNKNOWN 本身就是问题的根源，因此 CJ Date 在他下面引用的书中推荐在第4.5章中。避免 SQL 中的空值：

• Don't use the keyword UNKNOWN in any context whatsoever.

• 不要在任何上下文中使用关键字 UNKNOWN。

Read "ASIDE"on UNKNOWN, also linked below.

阅读UNKNOWN 上的“ASIDE”，也有链接如下。

6.8 NULLs and Host Languages (194)

However, you should know how NULLs are handled when they have to be passed to a host program. No standard host language for which an embedding is defined supports NULLs, which is another good reason to avoid using them in your database schema.

6.8 NULL 和宿主语言 (194)

但是，您应该知道在必须将 NULL 传递给主机程序时如何处理它们。没有定义嵌入的标准宿主语言支持 NULL，这是避免在数据库模式中使用它们的另一个很好的理由。

(separator)

（分隔器）

6.9 Design Advice for NULLs (195)

It is a good idea to declare all your base tables with NOT NULL constraints on all columns whenever possible. NULLs confuse people who do not know SQL, and NULLs are expensive.

6.9 NULL 的设计建议 (195)

尽可能在所有列上使用 NOT NULL 约束声明所有基表是一个好主意。NULL 会使不懂 SQL 的人感到困惑，而且 NULL 很昂贵。

Objection: NULLs confuses even people that know SQL well, see below.

反对意见： NULL 甚至会使熟悉 SQL 的人感到困惑，请参见下文。

(195)

NULLs should be avoided in FOREIGN KEYs. SQL allows this “benefit of the doubt” relationship, but it can cause a loss of information in queries that involve joins. For example, given a part number code in Inventory that is referenced as a FOREIGN KEY by an Orders table, you will have problems getting a listing of the parts that have a NULL. This is a mandatory relationship; you cannot order a part that does not exist.

(195)

应避免在 FOREIGN KEY 中使用 NULL。SQL 允许这种“怀疑的好处”关系，但它可能导致涉及连接的查询中的信息丢失。例如，假设 Inventory 中的零件编号代码被 Orders 表引用为 FOREIGN KEY，则在获取具有 NULL 的零件的列表时会遇到问题。这是一种强制性关系；您不能订购不存在的零件。

(separator)

（分隔器）

6.9.1 Avoiding NULLs from the Host Programs (197)

You can avoid putting NULLs into the database from the Host Programs with some programming discipline.

...

5. Determine impact of missing data on programming and reporting: Numeric columns with NULLs are a problem, because queries using aggregate functions can provide misleading results.

6.9.1 避免来自主机程序的 NULL (197)

您可以通过一些编程规则避免将主机程序中的 NULL 放入数据库中。

...

5. 确定缺失数据对编程和报告的影响： 带有 NULL 的数字列是一个问题，因为使用聚合函数的查询可能会提供误导性结果。

(separator)

（分隔器）

(227)

The SUM() of an empty set is always NULL. One of the most common programming errors made when using this trick is to write a query that could return more than one row. If you did not think about it, you might have written the last example as: ...

(227)

空集的 SUM() 始终为 NULL。使用此技巧时最常见的编程错误之一是编写可能返回多行的查询。如果你没有想过，你可能会把最后一个例子写成：...

(separator)

（分隔器）

10.1.1 Sources of NULLs (242)

It is important to remember where NULLs can occur. They are more than just a possible value in a column. Aggregate functions on empty sets, OUTER JOINs, arithmetic expressions with NULLs, and OLAP operators all return NULLs. These constructs often show up as columns in VIEWs.

10.1.1 NULL 的来源 (242)

记住 NULL 可能出现的位置很重要。它们不仅仅是列中的一个可能值。空集上的聚合函数、OUTER JOIN、带有 NULL 的算术表达式和 OLAP 运算符都返回 NULL。这些结构通常在 VIEW 中显示为列。

(separator)

（分隔器）

(301)

Another problem with NULLs is found when you attempt to convert IN predicates to EXISTS predicates.

(301)

当您尝试将 IN 谓词转换为 EXISTS 谓词时，会发现 NULL 的另一个问题。

(separator)

（分隔器）

16.3 The ALL Predicate and Extrema Functions (313)

It is counterintuitive at first that these two predicates are not the same in SQL:

...

But you have to remember the rules for the extrema functions—they drop out all the NULLs before returning the greater or least values. The ALL predicate does not drop NULLs, so you can get them in the results.

16.3 ALL 谓词和极值函数 (313)

乍一看，这两个谓词在 SQL 中并不相同，这是违反直觉的：

...

但是你必须记住极值函数的规则——它们在返回更大或最小的值之前删除所有的 NULL。ALL 谓词不会删除 NULL，因此您可以在结果中获取它们。

(separator)

（分隔器）

(315)

However, the definition in the standard is worded in the negative, so that NULLs get the benefit of the doubt. ...

As you can see, it is a good idea to avoid NULLs in UNIQUE constraints.

(315)

然而，标准中的定义是否定的，因此 NULL 得到了怀疑。...

如您所见，在 UNIQUE 约束中避免 NULL 是一个好主意。

Discussing GROUP BY:

讨论 GROUP BY：

NULLs are treated as if they were all equal to each other, and form their own group. Each group is then reduced to a single row in a new result table that replaces the old one.

NULL 被视为彼此相等，并形成自己的组。然后每个组都减少到新结果表中的一行，以替换旧的结果表。

This means that for GROUP BY clause NULL = NULL does not evaluate to NULL, as in 3VL, but it evaluate to TRUE.

这意味着对于 GROUP BY 子句，NULL = NULL 不会像 3VL 那样评估为 NULL，但它评估为 TRUE。

SQL standard is confusing:

SQL 标准令人困惑：

The ORDER BY and NULLs (329)

Whether a sort key value that is NULL is considered greater or less than a non-NULL value is implementation-defined, but...

... There are SQL products that do it either way.

In March 1999, Chris Farrar brought up a question from one of his developers that caused him to examine a part of the SQL Standard that I thought I understood. Chris found some differences between the general understanding and the actual wording of the specification.

ORDER BY 和 NULL (329)

是否认为 NULL 排序键值大于或小于非 NULL 值是实现定义的，但是...

... 有 SQL 产品可以做到这一点。

1999 年 3 月，Chris Farrar 向他的一位开发人员提出了一个问题，让他检查了 SQL 标准中我认为我理解的部分。Chris 发现规范的一般理解和实际措辞之间存在一些差异。

And so on. I think is enough by Celko.

等等。我认为 Celko 就足够了。

C. J. Date on SQL NULLs

SQL NULL 上的 CJ 日期

C. J. Date is more radical about NULLs: avoid NULLs in SQL, period. In fact, chapter 4 of his SQL and Relational Theory: How to Write Accurate SQL Codeis titled "NO DUPLICATES, NO NULLS", with subchapters "4.4 What's Wrong with Nulls?"and "4.5 Avoiding Nulls in SQL" (follow the link: thanks to Google Books, you can read some pages on-line).

CJ Date 对 NULL 更为激进：避免 SQL 中的 NULL，句号。事实上，他的SQL and Relational Theory: How to Write Accurate SQL Code 的第 4 章标题为“NO DUPLICATES, NO NULLS”，子章节为 “4.4 What's Wrong with Nulls？” 和“4.5 避免 SQL 中的空值”（点击链接：感谢 Google Books，您可以在线阅读一些页面）。

Fabian Pascal on SQL NULLs

Fabian Pascal 关于 SQL NULL

From its Practical Issues in Database Management - A Reference for the Thinking Practitioner(no excerpts on-line, sorry):

从数据库管理中的实际问题 - 思维实践者的参考（没有在线摘录，抱歉）：

10.3 Pratical Implications

10.3.1 SQL NULLs

... SQL suffers from the problems inherent in 3VL as well as from many quirks, complications, counterintuitiveness, and outright errors [10, 11]; among them are the following:

• Aggregate functions (e.g., SUM(), AVG()) ignore NULLs (except for COUNT()).
• A scalar expression on a table without rows evaluates incorrectly to NULL, instead of 0.
• The expression "NULL = NULL" evaluates to NULL, but is actually invalid in SQL; yet ORDER BY treats NULLs as equal (whatever they precede or follow "regular" values is left to DBMS vendor).
• The expression "x IS NOT NULL" is not equal to "NOT(x IS NULL)", as is the case in 2VL.

...

All commercially implemented SQL dialects follow this 3VL approach, and, thus, not only do they exibits these problems, but they also have spefic implementation problems, which vary across products.

10.3 实际意义

10.3.1 SQL NULL

... SQL 存在 3VL 固有的问题以及许多怪癖、复杂性、违反直觉和完全错误 [10, 11]；其中包括：

• 聚合函数（例如，SUM()、AVG()）忽略 NULL（COUNT() 除外）。
• 没有行的表上的标量表达式错误地计算为 NULL，而不是 0。
• 表达式“NULL = NULL”的计算结果为 NULL，但在 SQL 中实际上是无效的；然而 ORDER BY 将 NULL 视为相等（无论它们在“常规”值之前还是之后都留给 DBMS 供应商）。
• 表达式“x IS NOT NULL”不等于“NOT(x IS NULL)”，就像 2VL 中的情况一样。

...

所有商业实现的 SQL 方言都遵循这种 3VL 方法，因此，它们不仅存在这些问题，而且还存在特定的实现问题，这些问题因产品而异。

回答by Evan Carroll

Just because you don't know what two things are, does not mean they're equal. If when you think of NULLyou think of “NULL” (string) then you probably want a different test of equality like Postgresql's IS DISTINCT FROMAND IS NOT DISTINCT FROM

仅仅因为你不知道两件事是什么，并不意味着它们是平等的。如果您想到NULL“NULL”（字符串），那么您可能想要一个不同的相等性测试，例如 Postgresql 的IS DISTINCT FROMANDIS NOT DISTINCT FROM

From the PostgreSQL docs on "Comparison Functions and Operators"

来自关于“比较函数和运算符”的PostgreSQL 文档

expression IS DISTINCT FROMexpression

expression IS NOT DISTINCT FROMexpression

For non-null inputs, IS DISTINCT FROMis the same as the <>operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROMis identical to =for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than "unknown".

表达IS DISTINCT FROM表达

表达IS NOT DISTINCT FROM表达

对于非空输入，IS DISTINCT FROM与<>运算符相同。但是，如果两个输入都为 null，则返回 false，如果只有一个输入为 null，则返回 true。类似地，对于非空输入，IS NOT DISTINCT FROM与 相同，=但当两个输入都为空时返回真，只有一个输入为空时返回假。因此，这些构造有效地表现为 null 是一个正常的数据值，而不是“未知”。

回答by Michael Krelin - hacker

Maybe it depends, but I thought NULL=NULLevaluates to NULLlike most operations with NULL as an operand.

也许这取决于，但我认为NULL=NULL评估NULL喜欢大多数以 NULL 作为操作数的操作。

回答by onedaywhen

The concept of NULL is questionable, to say the least. Codd introduced the relational model and the concept of NULL in context (and went on to propose more than one kind of NULL!) However, relational theory has evolved since Codd's original writings: some of his proposals have since been dropped (e.g. primary key) and others never caught on (e.g. theta operators). In modern relational theory (truly relational theory, I should stress) NULL simply does not exist. See The Third Manifesto. http://www.thethirdmanifesto.com/

至少可以说，NULL 的概念是有问题的。Codd 在上下文中引入了关系模型和 NULL 的概念（并继续提出了不止一种 NULL！）然而，自 Codd 的原始著作以来，关系理论已经发展：他的一些提议已被放弃（例如主键）和其他人从未流行过（例如 theta 运算符）。在现代关系理论（真正的关系理论，我应该强调）中，NULL 根本不存在。见第三宣言。http://www.thethirdmanifesto.com/

The SQL language suffers the problem of backwards compatibility. NULL found its way into SQL and we are stuck with it. Arguably, the implementation of NULLin SQL is flawed (SQL Server's implementation makes things even more complicated due to its ANSI_NULLSoption).

SQL 语言存在向后兼容性的问题。NULL 进入了 SQL 并且我们被它困住了。可以说，NULL在 SQL 中的实现是有缺陷的（SQL Server 的实现由于它的ANSI_NULLS选项使事情变得更加复杂）。

I recommend avoiding the use of NULLable columns in base tables.

我建议避免在基表中使用 NULLable 列。

---

---

Although perhaps I shouldn't be tempted, I just wanted to assert a corrections of my own about how NULLworks in SQL:

虽然也许我不应该受到诱惑，但我只是想就NULLSQL 中的工作方式断言我自己的更正：

NULL= NULLevaluates to UNKNOWN.

NULL=NULL评估为UNKNOWN。

UNKNOWNis a logical value.

UNKNOWN是一个逻辑值。

NULLis a data value.

NULL是一个数据值。

This is easy to prove e.g.

这很容易证明，例如

SELECT NULL = NULL

SELECT NULL = NULL

correctly generates an error in SQL Server. If the result was a data value then we would expect to see NULL, as some answers here (wrongly) suggest we would.

在 SQL Server 中正确生成错误。如果结果是一个数据值，那么我们希望看到NULL，因为这里的一些答案（错误地）表明我们会看到。

The logical value UNKNOWNis treated differently in SQL DML and SQL DDL respectively.

逻辑值UNKNOWN分别在 SQL DML 和 SQL DDL 中被区别对待。

In SQL DML, UNKNOWNcauses rows to be removed from the resultset.

在 SQL DML 中，UNKNOWN导致从结果集中删除行。

For example:

例如：

CREATE TABLE MyTable
(
 key_col INTEGER NOT NULL UNIQUE, 
 data_col INTEGER
 CHECK (data_col = 55)
);

INSERT INTO MyTable (key_col, data_col)
   VALUES (1, NULL);

The INSERTsucceeds for this row, even though the CHECKcondition resolves to NULL = NULL. This is due defined in the SQL-92 ("ANSI") Standard:

该INSERT行成功，即使CHECK条件解析为NULL = NULL。这是在 SQL-92（“ANSI”）标准中定义的：

11.6 table constraint definition

3)

If the table constraint is a check constraint definition, then let SC be the search condition immediately contained in the check constraint definition and let T be the table name included in the corresponding table constraint descriptor; the table constraint is not satisfied if and only if

EXISTS ( SELECT * FROM T WHERE NOT ( SC ) )

is true.

11.6 表约束定义

3)

若表约束为校验约束定义，则设SC为校验约束定义中直接包含的搜索条件，T为对应表约束描述符中包含的表名；表约束不满足当且仅当

存在（从 T WHERE NOT (SC) 中选择 *）

是真的。

Read that again carefully, following the logic.

仔细阅读一遍，遵循逻辑。

In plain English, our new row above is given the 'benefit of the doubt' about being UNKNOWNand allowed to pass.

用简单的英语，我们上面的新行给出了关于存在UNKNOWN和允许通过的“怀疑的好处” 。

In SQL DML, the rule for the WHEREclause is much easier to follow:

在 SQL DML 中，WHERE子句的规则更容易遵循：

The search condition is applied to each row of T. The result of the where clause is a table of those rows of T for which the result of the search condition is true.

搜索条件应用于 T 的每一行。 where 子句的结果是 T 中搜索条件结果为真的那些行的表。

In plain English, rows that evaluate to UNKNOWNare removed from the resultset.

在简单的英语中，评估为的行UNKNOWN将从结果集中删除。

回答by Magnus

At technetthere is a good explanation for how null values work.

在technet 上，对空值的工作方式有一个很好的解释。

Null means unknown.

Null 表示未知。

Therefore the Boolean expression

因此布尔表达式

value=null

值=空

does not evaluate to false, it evaluates to null, but if that is the final result of a where clause, then nothing is returned. That is a practical way to do it, since returning null would be difficult to conceive.

不计算为 false，计算为 null，但如果这是 where 子句的最终结果，则不返回任何内容。这是一种实用的方法，因为很难想象返回 null。

It is interesting and very importantto understand the following:

了解以下内容很有趣且非常重要：

If in a query we have

如果在查询中我们有

where (value=@param Or @param is null) And id=@anotherParam

and

和

• value=1
• @param is null
• id=123
• @anotherParam=123

• 值=1
• @param 为空
• 编号=123
• @anotherParam=123

then

然后

"value=@param" evaluates to null
"@param is null" evaluates to true
"id=@anotherParam" evaluates to true

"value=@param" 计算结果为 null
"@param is null" 计算结果为 true
"id=@anotherParam" 计算结果为 true

So the expression to be evaluated becomes

所以要评估的表达式变成

(null Or true) And true

(null 或 true) 和 true

We might be tempted to think that here "null Or true" will be evaluated to null and thus the whole expression becomes null and the row will not be returned.

我们可能会认为这里的“null 或 true”将被评估为 null，因此整个表达式变为 null 并且不会返回该行。

This is not so. Why?

事实并非如此。为什么？

Because "null Or true" evaluates to true, which is very logical, since if one operand is true with the Or-operator, then no matter the value of the other operand, the operation will return true. Thus it does not matter that the other operand is unknown (null).

因为“null Or true”的计算结果为true，这是非常合乎逻辑的，因为如果Or运算符的一个操作数为true，那么无论另一个操作数的值如何，该操作都将返回true。因此，另一个操作数未知（空）并不重要。

So we finally have true=true and thus the row will be returned.

所以我们终于有了 true=true ，因此该行将被返回。

Note: with the same crystal clear logic that "null Or true" evaluates to true, "null And true" evaluates to null.

注意：“null 或 true”的计算结果为真，“null And true”的计算结果为 null，逻辑清晰。

Update:
Ok, just to make it complete I want to add the rest here too which turns out quite fun in relation to the above.

更新：
好的，为了使它完整，我也想在这里添加其余的内容，与上述内容相比，这很有趣。

"null Or false" evaluates to null, "null And false" evaluates to false. :)

“null 或 false”计算结果为 null，“null And false”计算结果为 false。:)

The logic is of course still as self-evident as before.

逻辑当然还是和以前一样不言而喻。

回答by armen

Because NULLmeans 'unknown value' and two unknown values cannot be equal.

因为NULL意味着“未知值”并且两个未知值不能相等。

So, if to our logic NULLN°1 is equal to NULLN°2, then we have to tell that somehow:

所以，如果我们的逻辑NULLN°1 等于NULLN°2，那么我们必须以某种方式告诉它：

SELECT 1
WHERE ISNULL(nullParam1, -1) = ISNULL(nullParam2, -1)

where known value -1N°1 is equal to -1N°2

其中已知值-1N°1 等于-1N°2

回答by AlexDev

The answers here all seem to come from a CS perspective so I want to add one from a developer perspective.

这里的答案似乎都来自 CS 的角度，所以我想从开发人员的角度添加一个。

For a developer NULL is very useful. The answers here say NULL means unknown, and maybe in CS theory that's true, don't remember, it's been a while. In actual development though, at least in my experience, that happens about 1% of the time. The other 99% it is used for cases where the value is not UNKNOWN but it is KNOWN TO BE ABSENT.

对于开发人员来说，NULL 非常有用。这里的答案说 NULL 意味着未知，也许在 CS 理论中这是真的，不记得了，已经有一段时间了。但在实际开发中，至少根据我的经验，这种情况发生的概率约为 1%。其他 99% 用于值不是未知但已知不存在的情况。

For example:

例如：

• Client.LastPurchase, for a new client. It is not unknown, it is known that he hasn't made a purchase yet.

• When using an ORM with a Tableper ClassHierarchymapping, some values are just not mapped for certain classes.

• When mapping a tree structurea root will usually have Parent = NULL

• And many more...

• Client.LastPurchase，对于新客户。这不是未知的，据了解他还没有进行购买。

• 当使用带有每个类层次结构表映射的 ORM 时，某些值只是没有映射到某些类。

• 在映射树结构时，根通常具有Parent = NULL

• 还有很多...

I'm sure most developers at some point wrote WHERE value = NULL, didn't get any results, and that's how they learned about IS NULLsyntax. Just look how many votes this question and the linked ones have.

我相信大多数开发人员在某个时候写过WHERE value = NULL，没有得到任何结果，这就是他们学习IS NULL语法的方式。看看这个问题和链接的问题有多少票。

SQL Databases are a tool, and they should be designed the way which is easiest for their users to understand.

SQL 数据库是一种工具，应该以用户最容易理解的方式设计它们。

回答by TomEberhard

The confusion arises from the level of indirection (abstraction) that comes about from using NULL.

混淆源于使用 NULL 所产生的间接（抽象）级别。

Going back to the "what's under the Christmas tree" analogy, "Unknown" describes the state of knowledge about what is in Box A.

回到“圣诞树下有什么”的比喻，“未知”描述了关于框 A 中什么的知识状态。

So if you don't know what's in Box A, you say it's "Unknown", but that doesn't mean that "Unknown" is inside the box. Something other than unknown is in the box, possibly some kind of object, or possibly nothing is in the box.

因此，如果您不知道方框 A 中的内容，您会说它是“未知”，但这并不意味着“未知”在方框内。盒子里有一些未知的东西，可能是某种物体，或者盒子里可能什么都没有。

Similarly, if you don't know what's in Box B, you can label your state of knowledge about the contents as being "Unknown".

同样，如果您不知道方框 B 中的内容，您可以将您对内容的了解状态标记为“未知”。

So here's the kicker: Your state of knowledge about Box A is equal to your state of knowledge about Box B. (Your state of knowledge in both cases is "Unknown" or "I don't know what's in the Box".) But the contents of the boxes may or may not be equal.

所以这里是踢球者：您对 Box A 的知识状态等于您对 Box B 的知识状态。（您在这两种情况下的知识状态都是“未知”或“我不知道盒子里有什么”。）但是盒子里的内容可能相同也可能不同。

Going back to SQL, ideally you should only be able to compare values when you know what they are. Unfortunately, the label that describes a lack of knowledge is stored in the cell itself, so we're tempted to use it as a value. But we should not use that as a value, because it would lead to "the content of Box A equals the content of Box B when we don't know what's in Box A and/or we don't know what's in Box B. (Logically, the implication "if I don't know what's in Box A and if I don't know what's in Box B, then what's in Box A = What's in Box B" is false.)

回到 SQL，理想情况下，您应该只在知道它们是什么时才能够比较它们。不幸的是，描述缺乏知识的标签存储在单元格本身中，因此我们很想将其用作值。但是我们不应该使用它作为一个值，因为它会导致“当我们不知道 Box A 中有什么和/或我们不知道 Box B 中有什么时，Box A 的内容等于 Box B 的内容。 （从逻辑上讲，“如果我不知道方框 A 中的内容，如果我不知道方框 B 中的内容，那么方框 A 中的内容 = 方框 B 中的内容”的含义是错误的。）

Yay, Dead Horse.

是的，死马。