空值 (SQL)

<image>

空值（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：

```sql
10 * NULL          -- Result is NULL
```

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

```sql
NULL / 0
```

### 字串连接

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

返回的 Null结果：

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

对于数据库实作来说并非都是如此。在 Oracle RDBMS中，例如 NULL和空字符串被认为是相同的，因此`'Fish'

NULL

'芯片'`字串连接的结果是 'Fish芯片'。

## NULL和三值逻辑的比较

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

```sql
SELECT 10 = NULL       -- Results in Unknown
```

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

```sql
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 相同的逻辑结果，是处理空值时会遇到的常见错误，下面的简单例子说明了这个谬误：

```sql
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特定函数来测试这些参数，虽然这样子可能更繁琐。

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

#### CASE表达式

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

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

```sql
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：

```sql
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表达式，并考虑两个相等的空值。

```sql
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。

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

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

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

```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）表中缺少的值的占位符，如下所示。

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(*)`，这是一个众所周知的副作用。

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

```sql
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。否则，返回第一个参数的值。

```sql
NULLIF(value1, value2)
```

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

```sql
CASE WHEN value1 = value2
     THEN
         NULL
     ELSE
         value1
END
```

### COALESCE

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

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

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

```sql
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表达式这样的：

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

变成：

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

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

## 数据类型为空和未知

`NULL` 字面量在SQL中是无类型的。

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

```sql
CAST (NULL AS INTEGER)
```

表示INTEGER类型的未知值。