空值 (SQL)

空值（英语：Null、NULL）是结构化查询语言中使用的特殊标记，是关系数据库中对数据属性未知或缺失的一种标识，用于指示数据库中不存在数据值。由关系数据库模型的创作者 E.F.科德所引入。SQL空值是用来满足真实关系数据库管理系统（RDBMS）中，支持“缺失信息与不适用的信息”的需求。科德还介绍了在数据库理论中使用小写的希腊字母（ω）符号来表示空值。在 SQL中则是以 NULL 用于标识空值的保留关键字。SQL null是一个状态，而不是一个值。这种用法与大多数编程语言完全不同，其中引用的空值意味着不指向任何对象。

这不应与 0 数值混淆。空值表示缺少值－而与零值不同，与缺乏答案的方式不同，作为“否”的答案。例如“亚当拥有多少本书？”这个问题，答案可能是“零”（他没有）或“空白”（不知道他拥有多少）。在数据库表格中，回报此问题的列结果，将从没有值（标记为Null）开始，并且在我们确定亚当没有书籍之前，并不会更新为值“零”。

数据库表主键的取值不能为空值。另外，数据库中的统计计算，一般将有空值的数据忽略不计。

应用

作为插入资料时，字段的默认值
查询时的条件式

在SQL的Where条件式去判断id字段是否为Null时，像是 where id = null 是无法正确执行的，必须写成 where id is null ；反之若要判断为id字段是否为非Null时，则用where id is not null

查询时的空值评估取代（n为评估式或字段名称，e为当n为null时要回传的内容）

coalesce(n,e)

被Oracle DB、Microsoft SQL Server、IBM DB2、Postgre SQL、MySQL等数据库支持

isnull(n,e)

被Microsoft SQL Server、Microsoft-Access等数据库支持

nvl(n,e)

被Oracle DB数据库支持

Nz(n,e)

被Microsoft-Access等数据库支持

历史

EF Codd 在一篇 1975年的ACM - SIGMOD FDT报告的论文中提到了空值作为表示关系模型中缺失数据的一种方法。Codd最常被引用的与Null语义相关的论文（如 SQL中所采用的）是他 1979年在 ACM Transactions on Database Systems上发表的论文，其中他还介绍了他的 Relational Model/Tasmania，尽管其他许多提议后面的文章一直很模糊。1979年他的论文2.3节详细描述了空值在做算术运算时散播的语义，以及比较空值时使用三值（三元）逻辑的比较；他还详细说明了对其它集合操作的空值处理（后者目前仍存在争议）。在数据库理论界，Codd最初的建议（1975,1979）现在被称为“Codd tables”。 Codd后来强化了他的要求，即在 ComputerWorld杂志上发表的一篇 1985年由两部分组成的文章中，所有 RDBMS都支持 Null来表示缺失的数据。

在 IBM System R中实现原型后，1986年的 SQL标准基本上采用了 Codd的建议。虽然 Don Chamberlin认为nulls（和重复行一起）是 SQL最有争议的特性之一，但他辩护 SQL中的 Nulls设计，并提出了实用的观点，认为这是系统对缺少信息支持最方便的形式，从而使程序员免受许多重复的应用程序级别检查（请参阅semipredicate问题），同时也为数据库设计人员提供了不愿意使用 Null的选项; 例如，为了避免众所周知的异常（在本文的语义部分讨论）。Chamberlin还认为，除了提供一些缺失值功能之外，对于空值的实践经验也会导致依赖于空值的其它语言特性，如某些分组构造和外连接。最后，他认为在实践中，Nulls最终也会被用作快速修补现有模式的一种方法，当它需要超出其原始意图的时候，不是为了丢失而是为了不适用的数据编码; 例如，一个数据库如果有每英里加仑的字段，需要快速新增值为电动汽车时。

Codd在其 1990年出版的“数据库管理关系模型第2版”中指出，SQL标准所要求的单一 Null 类型是不够的，应该用两种不同的 Null 类型标记来替代，以表明数据丢失的原因。在 Codd的书中，这两个 Null型标记分别被称为“A值”和“I值”，分别表示“缺失但适用”和“缺失但不适用”。 Codd的建议将要求扩展 SQL的逻辑系统以适应四值逻辑系统。由于这种额外的复杂性，具有不同定义类型的多个空值概念，尚未在数据库领域广泛接受。尽管如此，它仍然是一个活跃的研究领域，许多论文还在发表。

挑战

由于其相关的三值逻辑（3VL），它在 SQL连接中使用的特殊要求，以及聚合函数和 SQL分组操作符所需的特殊处理，空值一直是争议的焦点和争议的来源。计算机科学教授 Ron van der Meyden将各种问题总结为：“SQL标准中的不一致，意味着不可能将任何直观的逻辑语义归于 SQL中的空值处理”。尽管为解决这些问题提出了各种建议，但候选方案的复杂性阻碍了它们的广泛采用。

空值的散播

由于 Null不是数据值，而是缺少值的标记，因此以数学运算符使用 Null会给出未知结果。

算术运算

该结果由空值表示。下例中将 Null乘以 10将导致Null：

10 * NULL          -- Result is NULL

这可能会有出乎意料的结果。例如，当试图将 Null除以零时，平台可返回 Null值而不会抛出预期的“除以零－数据异常”。虽然这种行为不是由 ISO SQL标准定义的，但许多 DBMS供应商都以类似的方式处理这一操作。例如，Oracle，PostgreSQL，MySQL服务器和 Microsoft SQL Server平台都会针对以下内容返回空值结果：

NULL / 0

字符串连接

在 SQL中常见的字符串连接操作，在其中一个操作元为 Null时也会导致 Null。下例演示了使用 Null与 SQL 字符串连接运算符 || 返回的 Null结果：

'Fish ' || NULL || 'Chips'   -- Result is NULL

对于数据库实现来说并非都是如此。在 Oracle RDBMS中，例如 NULL和空字符串被认为是相同的，因此'Fish'|| NULL || '芯片'字符串连接的结果是 'Fish芯片'。

NULL和三值逻辑的比较

由于 Null不是任何数据域的成员，因此它不被视为“值”，而是指缺失值的标记（或占位符）。因此与 Null进行比较永远不会导致 True或 False，而总变成在三值逻辑的 Unknown 结果中。下面表达式的逻辑结果是将值 10 与 Null进行比较，得到的结果会成为未知：

SELECT 10 = NULL       -- Results in Unknown

但是，如果缺失值与操作结果无关，则对 Null的某些操作可以返回值。考虑下面的例子：

SELECT NULL OR TRUE   -- Results in True

在这种情况下，OR 左边的未知值这一事实是无关紧要的，因为 OR 的操作结果将是 True，而不管左边的值如何。

SQL实现了三值逻辑，因此 SQL的实现必须提供专门的三值逻辑（3VL）。SQL三值逻辑的规则如下表所示（ $p$ 和 $q$ 表示逻辑状态）“ SQL使用 AND，OR和 NOT 的真值表，对应于 Kleene和 Łukasiewicz三值逻辑的常见片段，有价值的逻辑（它们的含义定义不同，但 SQL没有定义这样的操作）

p	q	p OR q	p AND q	p = q
True	True	True	True	True
True	False	True	False	False
True	未知	True	未知	未知
False	True	True	False	False
False	False	False	False	True
False	未知	未知	False	未知
未知	True	True	未知	未知
未知	False	未知	False	未知
未知	未知	未知	未知	未知

p	NOT p
True	False
False	True
未知	未知

WHERE子句中的未知效应

在数据操纵语言（DML）和查询的比较谓词中，若遇到 SQL三值逻辑，该 WHERE子句会导致 DML语句仅对那些谓词评估为 True的列结果起作用；若是 INSERT，UPDATE 或者 DELETE 等 DML语句，则因为此谓词计算的结果为假、或是未知列，不会执行，并放弃 SELECT查询。将 Unknown 解释成 False 相同的逻辑结果，是处理空值时会遇到的常见错误，下面的简单例子说明了这个谬误：

SELECT *
FROM t
WHERE i = NULL;

上例的查询逻辑一定返回零列，因为 i 字段与 Null 比较的结果一定是传回“未知”，即使对于那些 i 为 Null 的资料列也是如此。未知结果导致 SELECT语句立即丢弃每一列。（但在实现中，一些 SQL工具将使用与 Null的比较来检索列。）

空值指定和3VL特定比较谓词

基本 SQL比较运算符在与 Null进行比较时，始终返回“未知”，因此 SQL标准提供了两个特定的 Null 比较谓词： IS NULL和 IS NOT NULL（使用后缀语法），来检测数据是不是空值。

SQL标准包含一个扩展 F571“真值测试”，它引入了三个额外的逻辑一元运算符（实际上，如果我们计算它们的否定，它们是其语法的一部分），也使用了后缀表示法。他们有以下真值表：

p	true	false	unknown
p IS TRUE	true	false	false
p IS NOT TRUE	false	true	true
p IS FALSE	false	true	false
p IS NOT FALSE	true	false	true
p IS UNKNOWN	false	false	true
p IS NOT UNKNOWN	true	true	false

F571扩展与 SQL中布尔数据类型的存在正交（本文稍后讨论），尽管语法相似，但 F571不会在语言中引入布尔值或三值文字。1999年，在布尔型数据类型被引入标准之前，F571扩展实际上存在于 SQL92中。然而，F571扩展只有少数系统实现；PostgreSQL是实现它的数据库之一。

加入三值逻辑中的其它运算符使 SQL 的三值逻辑功用完善，这意味着它的逻辑运算符可以表示（以组合）任何可能的三值逻辑函数。

在不支持 F571扩展的系统上可以遍历每种可能组合，使表达式 p Unknown的参数来模拟 IS UNKNOWN p，并使用 IS NULL或其他 NULL特定函数来测试这些参数，虽然这样子可能更繁琐。

WHERE子句的排中律

空值在其它构造式中的影响

连接(JOIN)

CASE表达式

SQL提供了两种风格的条件表达式。一为“简单 CASE”并像 switch 语句一样操作。另一在标准中称为“搜索 CASE”，并像 if ... elseif一样运行。

简单 CASE 表达式使用隐式相等比较，它们的执行和 DML的 WHERE 子句处理 Null 的规则是相同的。因此，一个简单 CASE 表达式无法直接检查 Null 的存在。在简单 CASE 表达式中检查 Null 总会导致结果为未知，如下所示：

SELECT CASE i WHEN NULL THEN 'Is Null'  -- This will never be returned
              WHEN    0 THEN 'Is Zero'  -- This will be returned when i = 0
              WHEN    1 THEN 'Is One'   -- This will be returned when i = 1
              END
FROM t;

不管 i 列所包含的的值是什么（即使它包含Null），该表达式的计算结果都为“未知” ，'Is Null' 字符串将永远不会返回。

另一方面，“搜索” CASE表达式可以使用谓词 IS NULL 和 IS NOT NULL 条件。下例显示如何使用搜索 CASE表达式来正确检查 Null：

SELECT CASE WHEN i IS NULL THEN 'Null Result'  -- This will be returned when i is NULL
            WHEN     i = 0 THEN 'Zero'         -- This will be returned when i = 0
            WHEN     i = 1 THEN 'One'          -- This will be returned when i = 1
            END
FROM t;

Oracle的 SQL 方言提供了一个内置函数 DECODE，可以用它来代替简单的 CASE表达式，并考虑两个相等的空值。

SELECT DECODE(i, NULL, 'Null Result', 0, 'Zero', 1, 'One') FROM t;

如果最后都没有与条件相匹配的结果，所有这些结构将会返回 NULL；它们有一个默认的 ELSE NULL 子句。

程序扩展中的 IF语句

SQL/PSM（SQL预存持续模块）定义 SQL的存储程序扩展，例如 IF 语句。但历史上主要的 SQL供应商都包含了他们自己专有的程序扩展。循环和比较的程序扩展在空值比较规则下运行，类似于 DML 语句和查询。以下存储程序的片段以 ISO SQL 标准格式演示了在 IF 语句中使用 3VL的 Null 。

IF i = NULL THEN
      SELECT 'Result is True'
ELSEIF NOT(i = NULL) THEN
      SELECT 'Result is False'
ELSE
      SELECT 'Result is Unknown';

该IF语句仅对那些评估为 True 的比较执行操作。对于评估为 False 或未知的 IF 语句，该语句将控制传递给 ELSEIF 子句，最后传递给 ELSE 子句。上面代码将因与 Null 的比较始终评估为未知，结果一定会是 'Result is Unknown' 。

分析SQL空值的语义

选择和预测：代表性弱

如果考虑连接或联合：甚至不表示弱

检查约束和外键

外连接

SQL 的外连接、左外连接和右外连接，会自动生成空值以作为结果表中缺失值的占位符。如对于左外连接会产生空值，代替左外连接操作后，右侧表中字段缺少值的列。下例使用两个表来示范左外连接操作产生的空值占位符。第一个表（Employee）包含员工编号和姓名，而第二个表（PhoneNumber）包含相关的员工编号和电话号码，如下。

Employee
ID	LastName	FirstName
1	Johnson	Joe
2	Lewis	Larry
3	Thompson	Thomas
4	Patterson	Patricia

PhoneNumber
ID	Number
1	555-2323
3	555-9876

以下示例 SQL查询对这两个表执行左外连接。

SELECT e.ID, e.LastName, e.FirstName, pn.Number
FROM Employee e
LEFT OUTER JOIN PhoneNumber pn
ON e.ID = pn.ID;

此查询生成的结果集演示 SQL如何使用 Null作为右侧（PhoneNumber）表中缺少的值的占位符，如下所示。

Query result
ID	LastName	FirstName	Number
1	Johnson	Joe	555-2323
2	Lewis	Larry	NULL
3	Thompson	Thomas	555-9876
4	Patterson	Patricia	NULL

聚合函数

SQL定义了聚合函数以简化服务端的数据聚合计算。除 COUNT(*)函数外，所有集合函数都会执行 Null-elimination 步骤，因此计算的最终结果中不包含 Null。

请注意，消除 Null不等于用零替换 Null。例如下表中 AVG(i)（求 i 字段的平均值）将给出与 AVG(j)不同的结果：

i	j
150	150
200	200
250	250
NULL	0

这里的 AVG(i) 是 200（150, 200 和 250的平均），而 AVG(j) 则是 150（150, 200, 250 和 0 的平均）。在 SQL 聚合函数中，AVG(z)不等于 SUM(z)/COUNT(*)，这是一个众所周知的副作用。

聚合函数的输出也可以是空值。这里是一个例子：

SELECT COUNT(*), MIN(e.Wage), MAX(e.Wage)
FROM Employee e
WHERE e.LastName LIKE '%Jones%';

此查询将始终输出一行，计算姓氏中包含“Jones”的员工人数，并给出这些员工的最低和最高工资。但是，如果没有员工符合给定的标准会发生什么？计算空集的最小值或最大值是不可能的，所以这些结果必须为NULL，表示没有答案。这不是未知值，它是表示没有值的空值。结果是：

COUNT(*)	MIN(e.Wage)	MAX(e.Wage)
0	NULL	NULL

当两个空值相等时：分组，排序和一些设置操作

由于 SQL 2003将所有 Null标记定义为彼此不相等，因此需要特殊定义才能在执行某些操作时将 Null分组在一起。SQL将“任何两个彼此相等的值或任何两个空值”定义为“没有不同”（not distinct）。这允许 SQL在使用 GROUP BY 子句时对 Null 进行分组和排序。

其他 SQL操作，子句和关键字在处理空值时使用“没有不同”（not distinct）。这些包括以下内容：

PARTITION BY 排序和开窗功能的 ROW_NUMBER子句
UNION，INTERSECT和 EXCEPT操作符，它们将NULL视为用于行比较/消除目的相同
DISTINCT SELECT查询中使用的关键字

SQL规范中的 UNION操作符效果会违反空值不相等的原则（它们确实相互确定了空值）。因此某些 SQL操作（如联合或区别）可能产生不能表示确定信息的结果，这与涉及与 NULL进行显式比较的操作（例如 WHERE上面讨论的子句中的那些操作）不同。在 1979年的 Codd提案中（基本上被SQL92采纳），通过论证删除集合操作中的重复项“在比检索操作的评估中的等式测试更低的细节层次上发生”这种语义不一致性是合理的。

SQL标准没有明确定义空值的默认排序顺序。相反，在符合性的系统上，子句 ORDER BY可以分别使用列表 NULLS FIRST或 NULLS LAST，分别在所有数据值之前或之后，对空值进行排序。然而，并非所有数据库供应商都实现了这一功能。不实现此功能的供应商可能会在 DBMS中，为空值的排序指定不同的处理方法。

对索引操作的影响

某些 SQL产品不可以建立包含 NULL 的索引键。例如，PostgreSQL 8.3 版本之前 B树索引的文件说明，

B－树可以处理数据的等式和范围查询，这些数据可以按照某种顺序排序。特别是，PostgreSQL查询规划器会在使用< ≤ = ≥ > 这些运算符进行比较时，涉及索引列时考虑使用 B－树索引：
< ≤ = ≥ > 等效于这些运算符（如 BETWEEN 和 IN）组合的构造也可以用 B－树索引搜索实现。（但请注意，IS NULL 不是等于而且不可索引。）:

在强制唯一性的建立索引执行情况下，字段为 NULL值会从索引中被排除，并且不会在 NULL之间强制执行唯一性。再次引用 PostgreSQL文件：

当索引被宣告是唯一时，将不允许具有相同索引值的多个表行。空值不被视为相等。多列唯一索引只会拒绝所有索引列在两行中相等的情况。

这种作法符合 SQL：2003-标量空值比较的规范。

索引空值的另一种方法涉及按照 SQL：2003定义的行为将它们处理为不明显。例如，Microsoft SQL Server文档声明如下：

为了建立索引，NULL比较相等。因此，如果键在多行中为 NULL，则无法创建唯一索引或 UNIQUE 约束。当选择唯一索引或唯一约束的列时，选择定义为 NOT NULL 的列。

这两种索引策略都与 SQL：2003定义的 Null行为一致。因为索引方法没有被 SQL：2003标准明确定义，所以空值的索引策略完全由供应商来设计和实现。

空值处理函数

SQL定义了两个函数来显式处理空值：NULLIF 和 COALESCE。这两个函数都是 CASE表达式的缩写。

NULLIF

NULLIF 函数接受两个参数。如果第一个参数等于第二个参数，则 NULLIF 返回 Null。否则，返回第一个参数的值。

NULLIF(value1, value2)

因此，NULLIF是以下 CASE表达式的缩写：

CASE WHEN value1 = value2
     THEN
         NULL
     ELSE
         value1
END

COALESCE

COALESCE 函数实现了空值结合运算符。接受参数表表，从列表中返回第一个非 Null值：

COALESCE(value1, value2, value3, ...)

COALESCE被定义为以下SQL CASE表达式的简写：

CASE WHEN value1 IS NOT NULL THEN value1
     WHEN value2 IS NOT NULL THEN value2
     WHEN value3 IS NOT NULL THEN value3
     ...
     END

一些 SQL DBMS 供应商实现特定的功能类似 COALESCE 函数。某些系统（例如 Transact-SQL）实现为 ISNULL函数，或者功能类似 COALESCE 的其他函数。

NVL

ORACLE NVL函数接受两个参数。它返回第一个非 NULL参数，如果所有参数都是 NULL，则返回 NULL。

COALESCE 表达式可被转换成等效的 NVL表达式这样的：

COALESCE ( val1, ... , val{n} )

变成：

NVL( val1 , NVL( val2 , NVL( val3 , … , NVL ( val{n-1} , val{n} ) … )))

这个函数的用例是在一个表达式中用某一特定值替换 NULL，例如 NVL(SALARY, 0)，意为'若薪资字段缺少值，则以 0 替换它'。但有一个明显例外，在大多数实现中 COALESCE 只评估其参数表表到逹第一个非 NULL值，这有几个重要的原因：第一个非 NULL 参数之后的参数可能是一个函数，它可能在计算成本上很昂贵、无效、或者可能会产生意料之外的副作用；然而 NVL 将评估参数表表其中的所有参数。

数据类型为空和未知

NULL 字面量在SQL中是无类型的。^[1]

SQL-92引入了CAST，允许把NULL字面量强制转为特定类型的Null，例如

CAST (NULL AS INTEGER)

表示INTEGER类型的未知值。

BOOLEAN数据类型

争论

常见错误

批评

闭环世界的假设

参考文献

^ Jim Melton; Alan R. Simon. SQL:1999: Understanding Relational Language Components. Morgan Kaufmann. 2002: 53. ISBN 978-1-55860-456-8.

延伸读物

外部链接

参见

SQL

[Understanding1999-1] Jim Melton; Alan R. Simon. SQL:1999: Understanding Relational Language Components. Morgan Kaufmann. 2002: 53. ISBN 978-1-55860-456-8.

[1]