为什么在SQL Server中NULL = NULL的结果为false？

Question

为什么在SQL Server中NULL = NULL的结果为false？

181

在SQL Server中，如果你在where子句中有nullParam=NULL，它总是会被评估为false。这是违反直觉的，并且导致了许多错误。我知道使用IS NULL和IS NOT NULL关键字是正确方式。但是为什么SQL Server会以这种方式行事？

- Byron Whitlock

190

我没有姐妹，我的朋友也没有。如果“NULL = NULL”，那么我们有一个共同的姐妹，因此我们是亲戚！ :) - Matt Hamilton

15

关于SQL NULL值存在长期争议（例如参见：http://en.wikipedia.org/wiki/Null_%28SQL%29#Controversy和http://www.firstsql.com/inulls.htm）。具体问题在于相等性是一个长期以来的数学概念，而SQL却违反了它 - 相等性是自反的：对于每个x，x = x。这必须始终成立，否则就引入了不符合标准的相等性解释，混淆也就是显而易见的结果。 - MaD70

17

这完全不违背数学原理。我在想两个数字，但我不会告诉你它们是什么。现在你告诉我，它们相等吗？ - Tom H

11

@Matt，我不同意你的类比。 NULL = NULL 不会意味着你们有一个共同的姐妹，而是意味着你们都没有姐妹。 - reustmd

6

@manu08 不，当前的实现方式（即NULL永远不等于NULL）意味着我们俩都没有姐妹，这也是我的意思。 - Matt Hamilton

显示剩余8条评论

21个回答

154

弗兰克多大了？我不知道（null）。

雪莉多大了？我不知道（null）。

弗兰克和雪莉年龄相同吗？

正确答案应该是“我不知道”（null），而不是“否”，因为弗兰克和雪莉可能年龄相同，我们只是不知道。

- Neil McGuigan

11

我不同意null表示“未知”。它实际上的意思是“没有数据”。它可能用于表示信息未知的情况，但更常用的是表示某些东西不存在。继续你的例子：Frank的中间名是什么？他没有（null）。Shirley的中间名是什么？她没有（null）。Frank和Shirley有相同的中间名吗？是？不是？不知道？我能理解“不是”和“不知道”的主张，但除非你过于字面，否则没有真正支持“是”的论据。 - Richiban

4

我不同意。某行不存在意味着“没有数据”。 - Neil McGuigan

2

@NeilMcGuigan 如果数据有自己的表格，那么这是正确的。但是如果数据只是在一列中表示呢？难道不应该使用“null”来表示数据不存在吗？“未知”是数据缺失的一个非常具体的原因。 - Richiban

6

但是 null = null 的结果是 FALSE，而不是 NULL。 - slartidan

1

@slartidan 我同意你的观点，但是那是不正确的。 - Neil McGuigan

显示剩余7条评论

31

我希望在这里阐明我的立场。

NULL = NULL 等于 FALSE 是错误的。Hacker 和 Mister 正确回答了 NULL。以下是原因。Dewayne Christensen 在评论中写给我，他对 Scott Ivey 说：

既然现在是十二月，我们来举个季节性的例子。我有两份礼物放在树下。你告诉我这两份礼物是不是一样的。

它们可以不同，也可以相同，除非打开两份礼物，否则你不知道。谁知道呢？你邀请了两个不认识彼此的人，他们都送了你同样的礼物-罕见，但并非不可能的§。

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

这个例子旨在说明“..（根据您的系统，为 false 或 null）..”是一个正确的答案-不是，只有在三值逻辑中 NULL 是正确的（或者您接受一个会给出错误答案的系统吗？）

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

三值逻辑（3VL）是反直觉的（请查看Stackoverflow和其他论坛上关于此主题的无数其他问题以确保）；
基于SQL的DBMS通常甚至不尊重3VL，有时会给出错误的答案（正如原帖作者在这种情况下所断言的那样，SQL Server就是如此）。

因此，我再次强调：SQL强制人们解释等式的自反性并没有任何好处，即：

对于任何x，x = x §§（用简单的英语说：无论领域如何，“东西”总是等于它本身）。

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

还要注意，NULL是有效的“非值”（正如他们的辩护者所声称的那样），可以将它们分配为关系变量的属性值之一。因此，它们是每种类型（域）的可接受值，而不仅仅是逻辑表达式类型的值。

而这就是我的观点：NULL作为一个值，是一个“奇怪的野兽”。毫不客气地说，我更喜欢说：没有意义。

我认为这个表述更清晰，更不容易争议 - 对我英语水平的不足感到抱歉。

这只是NULL的问题之一。尽可能避免使用它们会更好。

§ 我们关心的是值，因此两个礼物始终是两个不同的物理对象这一事实并不是一个有效的反驳；如果您不信服，我很抱歉，这不是解释价值和“对象”语义差异的地方（关系代数从一开始就具有值语义 - 参见Codd的信息原则；我认为一些SQL DBMS实现者甚至不关心公共语义）。

§§ 据我所知，这是一个公理，自古以来以一种形式或另一种形式被接受，并且总是在2VL中解释（但我不确定首次开发时间）。3VLs（实际上是一个逻辑家族）是一个更近期的发展。

旁注： 如果有人引入Bottom，Unit和Option类型试图证明SQL NULLs的合理性，只有经过相当详细的审查，才能证明SQL实现与NULLs具有完整的类型系统，并最终澄清NULLs（这些“不完全是值的值”）到底是什么。

在接下来的内容中，我将引用一些作者。任何错误或遗漏可能是我的问题而不是原始作者的问题。

Joe Celko关于SQL NULLs

我经常在这个论坛上看到Joe Celko的引用。显然他在这里是一个备受尊敬的作者。所以，我问自己：“他是如何解释SQL NULLs的众多问题的呢？”我的一个朋友有一本电子书版本的《Joe Celko's SQL for smarties: advanced SQL programming, 3rd edition》。让我们看看。

首先，目录。最让我惊讶的是NULL被提到的次数以及它出现的各种情况：

3.4 算术和 NULLs 109
3.5 转换值到和从 NULL 110
3.5.1 NULLIF() 函数 110
6 NULLs: SQL中缺失的数据 185
6.4 比较 NULLs 190
6.5 NULLs 和逻辑 190
6.5.1 子查询谓词中的 NULLS 191
6.5.2 标准SQL解决方案 193
6.6 数学和 NULLs 193
6.7 函数和 NULLs 193
6.8 NULLs 和主机语言 194
6.9 NULLs 的设计建议 195
6.9.1 避免来自主机程序的NULLs 197
6.10 多个NULL值的注意事项 198
10.1 IS NULL 谓词 241
10.1.1 NULLs 的来源 242
...

等等。对我来说，这听起来像是一个讨厌的特殊情况。

我将引用本书的一些摘录，尽量限制在必要的范围内，出于版权原因。我认为这些引用属于“公平使用”原则，它们甚至可以激励购买这本书——所以我希望没有人会抱怨（否则我将不得不删除大部分或全部内容）。此外，出于同样的原因，我将避免报告代码片段。对此感到抱歉。购买这本书以了解详细的推理。

以下内容中括号内为页码。

非空约束 (11)

最重要的列约束是非空约束，它禁止在列中使用NULL值。通常使用此约束，并仅在有充分理由时删除它。在对数据进行查询时，它可以帮助您避免空值的复杂性。

它不是一个值，它只是一个占位符。

再次提到“既不是值又不是值”的无聊说法。其余部分对我来说似乎相当合理。

(12)

简而言之，NULL值会引起SQL中许多不规则特性，我们将在稍后讨论。当您无法避免使用NULL时，您最好记住情况和NULL值的规则。

关于SQL中的NULL值和无穷大:

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

SQL出于几个原因未接受IEEE数学模型。

...

如果在SQL中允许使用IEEE数学规则，则需要无限类型转换规则以及在转换后表示无限精确数值的方法。人们已经对NULL值感到困扰了，所以让我们不要走那条路。

SQL实现对特定上下文中NULL值的真正含义尚未确定：

3.6.2 指数函数 (116)

问题在于当 (x <= 0) 时，对数是未定义的。一些 SQL 实现会返回错误信息，一些会返回 NULL，而 DB2/400 版本 3 发布 1 则将 *NEGINF（缩写为“负无穷”）作为其结果。

Joe Celko 引用 David McGoveran 和 C. J. Date 的话:

6 NULLs: SQL 中的缺失数据 (185)

在他们的书《Sybase 和 SQL Server 指南》中，David McGoveran 和 C. J. Date 说：“本作者认为，NULLs，至少在当前 SQL 中的定义和实现中，比它们的价值更麻烦，应该避免使用；它们显示非常奇怪和不一致的行为，并且可能成为错误和混淆的丰富源泉。（请注意，这些评论和批评适用于任何支持 SQL 风格 NULLs 的系统，而不仅仅是针对 SQL Server 特定的系统。）”

NULLs 就像毒品成瘾:

(186/187)

在本书的其余部分中，我将敦促您不要使用它们，这可能看起来矛盾，但事实并非如此。把 NULL 看作毒品；适当使用它可以为您工作，但滥用它会毁掉一切。您最好的策略是尽可能避免使用 NULLs，并在必要时正确使用它们。

我的独特反对意见是“正确使用它们”，这与具体的实现行为相互作用不佳。

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

人们往往忘记子查询中隐藏了一个与 NULL 的比较。考虑下面两个表：

...

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

(separator)

6.5.2 标准SQL解决方案（193）

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

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

但UNKNOWN本身就是问题的来源，因此C.J.Date在他下面引用的书的第4.5章“避免 SQL 中的 Nulls”中建议：

不要在任何情况下使用关键字UNKNOWN。

请阅读下面链接中关于UNKNOWN的"ASIDE"。

6.8 NULL值和宿主语言 (194)

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

(分隔符)

6.9 NULL值的设计建议 (195)

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

反驳：NULL值甚至会让熟悉SQL的人感到困惑，见下文。

(195)

应该避免在外键中使用NULL值。SQL允许这种“容忍”关系，但它会导致涉及连接的查询中信息的丢失。例如，在Inventory中引用为外键的零件编号代码被Orders表引用时，您将无法获取具有NULL值的零件列表。这是一种强制性的关系；您不能订购不存在的零件。

(分隔符)

6.9.1 避免从宿主程序插入NULL值 (197)

您可以通过一些编程纪律避免从宿主程序将NULL值放入数据库。

...

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

(分隔符)

(227)

一个空集合的SUM()总是NULL。在使用这个技巧时最常见的编程错误之一是编写可能返回多行的查询。如果你没有考虑到这一点，你可能会把最后一个例子写成: ...

(分隔符)

10.1.1 NULL值的来源 (242)

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

(分隔符)

(301)

将IN谓词转换为EXISTS谓词时，另一个问题出现了。

(分隔符)

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

起初，在SQL中这两个谓词不相同是反直觉的:

...

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

(分隔符)

(315)

然而，标准中的定义是用否定语言表述的，因此NULL会获得怀疑的好处。 ...

正如您所看到的，避免在UNIQUE约束中使用NULL是一个好主意。

讨论GROUP BY：

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

这意味着对于GROUP BY子句，NULL = NULL不会评估为NULL，如3VL中那样，而是评估为TRUE。

SQL标准很令人困惑：

ORDER BY和NULLs（329）

排序键值为NULL时，它是被认为大于还是小于非NULL值是实现定义的，但是......

......有一些SQL产品可以采用任一方式。

1999年3月，Chris Farrar提出了他的一个开发人员的问题，使他检查了我以为我理解的SQL标准的一部分。 Chris发现了一些普遍理解和规范实际措辞之间的差异。

等等。我认为Celko已经足够了。

C. J. Date关于SQL NULLs

C. J. 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 Avoiding Nulls in SQL”（通过链接可以查看一些在线页面，感谢Google Books）。

Fabian Pascal关于SQL NULLs的观点：

来自他的《Practical Issues in Database Management - A Reference for the Thinking Practitioner》（很抱歉没有在线摘录）。

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方法，因此，它们不仅存在这些问题，而且它们还具有特定的实现问题，这些问题因产品而异。

- MaD70

4

这是我的观点：NULL作为值是一个“奇怪的生物”。这是因为NULL并不是一个值。 - Pavel Minaev

1

此外，SQL Server 不会给出 (NULL = NULL) -> FALSE。引用 ANSI_NULLS 的文档：“当指定为 ON 时，所有与 null 值的比较都会评估为 __UNKNOWN__。当指定为 OFF 时，对非 UNICODE 值与 null 值的比较将在两个值都为 NULL 时评估为 TRUE。” - Pavel Minaev

1

既然已经到了十二月，让我们用一个季节性的例子。我在树下有两个礼物。现在，你告诉我这两个礼物是不是一样的。......是的，就目前而言，你得到了两件东西，就你目前的知识范围而言，它们对你来说是完全相同的。 - Bradley Thomas

简而言之，NULL = NULL 的结果为假。 - Vishwanath Dalvi

4

null = null 应该是 true。null 是一个定义良好的值，它可以表示“未知”的值，但也可能表示不存在的值。应由开发人员决定 null 表示什么，但 null 本身绝对是一个值，null 等于 null。任何其他实现都注定会出现问题，因为它会在本质上是布尔值的谓词中插入三段逻辑。我对 SQL server 中这成为永久性设置感到震惊。把它关掉。 - Triynko

显示剩余4条评论

12

这里的回答似乎都是从计算机科学的角度来看的，所以我想从开发者的角度补充一个。

对于开发者来说，NULL非常有用。这里的回答说NULL表示未知，在计算机科学理论上可能是正确的，不记得了，已经有一段时间了。然而，在实际开发中，至少根据我的经验，这种情况只发生了大约1%的时间。而其他99%的情况下，NULL用于表示值不是未知，而是已知为不存在的情况。

例如：

当使用软删除时，deletedAt = NULL并不意味着我们不知道何时被删除，而是意味着它没有被删除。
Client.LastPurchase对于一个新客户来说，它并不是未知的，而是已知他尚未进行过购买。
当使用ORM与表对类的层次结构映射时，某些类别的某些值只是没有被映射。
当映射一个树形结构时，根节点通常会有Parent = NULL
还有很多其他的...

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

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

- AlexDev

2

每个人似乎都在喊“NULL是未知的”，然后为这种行为辩护。是的，如果这是前提，那么三值逻辑可能是答案。但在我工作的几乎所有数据库中，NULL表示缺席。很抱歉，@AlexDev，你的声音在荒野中消失了。 - John Rees

11

仅因为您不知道两个东西是什么，并不意味着它们相等。如果当您想到 NULL 时，您会想到“NULL”（字符串），那么您可能需要使用不同的等于测试，比如PostgreSQL的 IS DISTINCT FROM 和 IS NOT DISTINCT FROM。

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

expression IS DISTINCT FROM expression

expression IS NOT DISTINCT FROM expression

对于非null输入，IS DISTINCT FROM 等同于 <> 运算符。但是，如果两个输入均为 null，则返回 false，如果只有一个输入为 null，则返回 true。类似地，IS NOT DISTINCT FROM 对于非null输入与 = 相同，但是当两个输入均为null时返回true，而只有一个输入为null时返回false。因此，这些结构实际上就像null是正常数据值一样，而不是“未知的”。

- Evan Carroll

9

也许这取决于具体情况，但我认为NULL=NULL的结果会像大多数与NULL作为操作数的运算一样，得到NULL。

- Michael Krelin - hacker

7

MSDN有一篇关于null和它们引发的三态逻辑的描述性文章。

简而言之，SQL92规范将NULL定义为未知，并且在以下操作中使用NULL会导致未经训练者出现意外结果：

= operator NULL   true   false 
NULL       NULL   NULL   NULL
true       NULL   true   false
false      NULL   false  true

and op     NULL   true   false 
NULL       NULL   NULL   false
true       NULL   true   false
false      false  false  false

or op      NULL   true   false 
NULL       NULL   true   NULL
true       true   true   true
false      NULL   true   false

- Paul Wagland

但问题不在于三值逻辑，而在于等式的自反性质。 - MaD70

更准确地说，正如我在我的答案中最后详细说明的那样，当等式在3VL中被解释时，问题就会出现，这样等式的自反性质并不总是为真。 - MaD70

7

在 technet 上有一个很好的解释，关于空值的工作原理。

空值意味着未知。

因此，布尔表达式

value=null

不会计算为false，它计算为null，但如果这是where子句的最终结果，那么什么也不返回。这是一种实用的方式，因为返回null将很难想象。

以下内容非常重要，需要认真理解：

如果在查询中我们有

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

并且

value=1
@param为空
id=123
@anotherParam=123

那么"value=@param"的值为null,
"@param为空"的值为true
"id=@anotherParam"的值也为true

因此，要评估的表达式变成了

(null或true)且true

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

但事实并非如此。为什么？

因为"null或true"评估为true，这是非常合理的，因为如果一个操作数与Or运算符为真，则无论另一个操作数的值如何，该操作都将返回true。因此，其他操作数未知（null）是无关紧要的。

所以我们最终得到true=true，因此该行将被返回。

注意：使用相同的清晰逻辑，“null和true”评估为null时，“null或false”评估为null，“null和false”评估为false。

更新：
好吧，为了使其完整，我想在这里添加其余部分，它与以上文本有趣的关系。

"null或false"评估为null，"null和false"评估为false。 :)

当然，逻辑仍然像以前一样不言而喻。

- Magnus

5

概念“NULL”是值得质疑的。Codd引入了关系模型和上下文中的“NULL”概念（并提出了不止一种“NULL”！）。然而，自Codd最初的著作以来，关系理论已经发展：他的一些建议已被放弃（例如主键），而其他建议从未流行过（例如theta运算符）。在现代关系理论中（真正的关系理论，我应该强调），“NULL”根本不存在。请参阅第三宣言。http://www.thethirdmanifesto.com/ SQL语言存在向后兼容性问题。“NULL”进入了SQL，并且我们被卡住了。可以说，在SQL中实现“NULL”的方式存在缺陷（由于其“ANSI_NULLS”选项，SQL Server的实现使事情变得更加复杂）。

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

尽管我可能不应该被诱惑，但我想要关于SQL中NULL的工作原理做出自己的更正：

NULL = NULL评估为UNKNOWN。

UNKNOWN是一个逻辑值。

NULL是一个数据值。

例如，这很容易证明：

SELECT NULL = NULL

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

在SQL DML和SQL DDL中，逻辑值UNKNOWN的处理方式有所不同。

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

例如：

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);

这一行的INSERT成功了，尽管CHECK条件解析为NULL = NULL。这是由SQL-92（“ANSI”）标准中定义的原因：

11.6 表约束定义

3) 如果表约束是检查约束定义，则让SC成为立即包含在检查约束定义中的搜索条件，并让T成为相应表约束描述符中包含的表名；当且仅当

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

为真时，不满足表约束。

请仔细阅读并遵循逻辑。

简单来说，我们上面的新行被赋予了“未知”的“优势”，并被允许通过。

在SQL DML中，WHERE子句的规则要容易得多：

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

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

- onedaywhen

5

由于NULL表示“未知值”，两个未知值不能相等。

因此，如果根据我们的逻辑NULL N°1等于NULL N°2，则我们必须以某种方式说明：

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

已知值为-1的N°1等同于-1的N°2

- armen

nullParam1 = -1 and nullParam2 =NULL and airplain crash .... should be ISNULL(NULLIF(@nullParam1, @nullParam2), NULLIF(@nullParam2, nullParam1)) IS NULL - Selvin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Scott Ivey · Accepted Answer

在这种情况下，将null视为“未知”（或“不存在”）。在任何一种情况下，你都不能说它们相等，因为你不知道它们的值。因此，null=null评估为不是true（false或null，具体取决于你的系统），因为你不知道值是否相等。这种行为在ANSI SQL-92标准中定义。

编辑：这取决于你的ansi_nulls设置。如果你关闭了ANSI_NULLS，则会评估为true。运行以下代码进行示例...

set ansi_nulls off

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


set ansi_nulls ON

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