这是我参与更文挑战的第4天，活动详情查看：更文挑战

NULL 即是空

在数据库中，空值（NULL）是一个特殊的值，通常用于表示缺失值或者不适用的值。比如，填写问卷时不愿意透露某些信息会导致录入项的缺失，在公司的组织结构中总会有一个人（董事长 / 总经理）没有上级领导。

首先一点，空值与数字 0 并不相同。假如我问你：你的钱包里有多少钱？如果你知道里面没有钱，可以说是零；如果你不确定，那么就是未知，但不能说没有。当我们需要创建一个表来存储这个信息的时候，应该是 NULL；除非我们能够确定钱包里面没有钱或者有多少钱。

另外，空值与空字符串（”）也不相同，原因和上面类似。但是 Oracle 是一个例外，我们会在下文具体讨论。

在大多数编程语言中，访问 null 值通常会导致错误；但是 SQL 不会出错，只是会影响到运算的结果而已。

三值逻辑

在大多数编程语言中，逻辑运算的结果只有两种情况，不是真（True）就是假（False）。但是对于 SQL 而言，逻辑运算还可能是未知（Unknown）：

引入三值逻辑主要是为了支持 NULL，因为 NULL 代表的是未知数据。因此，SQL 中的逻辑运算与（AND）、或（OR）以及非（NOT）的结果如下：

对于 AND 运算符而言，真和未知的与运算有可能是真，也有可能是假；因此，最终的结果是未知。

?SQL 中的 WHERE、HAVING 以及 CASE WHEN 子句只返回逻辑运算结果为真的数据，不返回结果为假或未知的数据。

空值比较

当我们使用比较运算符（=、<>、<、> 等）与 NULL 进行比较时，结果既不是真也不是假，而是未知；因为 NULL 表示未知，也就意味着可能是任何值。以下运算的结果都是未知：

NULL = 0
NULL <> 0
NULL <= 0
NULL = NULL
NULL != NULL
复制代码

NULL 与任何值都不相等，甚至两个 NULL 也不想等；因为我们不能说两个未知的值相同，也不能说它们不相同。

⚠️对于比较运算而言，NULL 和 NULL 不相同；但是某些 SQL 子句中的 NULL 值被看作相同的值，例如 GROUP BY。具体参考下文。

那么，如何判断一个值是否是 NULL 呢？为此，SQL 引入了两个谓词（WHERE 子句）：IS NULL和IS NOT NULL。以下示例用于查找 manager 为空的员工：

— 使用比较运算符判断空值


SELECT employee\_id, first\_name, last\_name, manager\_id
  FROM employees
 WHERE manager\_id = NULL;
employee\_id|first\_name|last\_name|manager\_id|
复制代码

— 使用 IS NULL 判断空值

SELECT employee\_id, first\_name, last\_name, manager\_id
  FROM employees
 WHERE manager\_id IS NULL;
employee\_id|first\_name|last\_name|manager\_id|

-----------|----------|---------|----------|
        100|Steven    |King     |          |
复制代码

其中，第一个查询使用比较运算符判断空值，不会返回任何结果；第二个查询使用 IS NULL 判断空值，返回了正确的结果。

除了标准的IS [NOT] NULL之外，还有一些数据库扩展的运算符可以用于空值比较：

— MySQL

SELECT employee\_id, first\_name, last\_name, manager\_id
  FROM employees
 WHERE manager\_id <=> NULL;
employee\_id|first\_name|last\_name|manager\_id|
-----------|----------|---------|----------|
        100|Steven    |King     |          |
复制代码

— PostgreSQL

SELECT employee\_id, first\_name, last\_name, manager\_id
  FROM employees
 WHERE manager\_id IS NOT DISTINCT FROM NULL;
employee\_id|first\_name|last\_name|manager\_id|
-----------|----------|---------|----------|
        100|Steven    |King     |          |
复制代码

MySQL 中的<=>可以用于等值比较，支持两个 NULL 值；PostgreSQL 中的IS [NOT] DISTINCT FROM可以用于等值比较，支持两个 NULL 值。

以下查询的结果也不会返回任何结果：

SELECT employee\_id, first\_name, last\_name, manager\_id
  FROM employees
 WHERE (1 = NULL) OR (1 != NULL);
复制代码

因为根据上面的三值逻辑，两个未知结果的 OR 运算最终还是未知。

前文我们说过，空字符串不是 NULL；但是 Oracle 中的空字符串被看作 NULL。例如：

— Oracle

SELECT 1
  FROM dual
 WHERE '' IS NULL;
VAL|
---|
  1|
复制代码

— 其他数据库

SELECT 1 AS val
 WHERE '' IS NULL;
val|
---|
复制代码

当然，我们如果使用等值（=）运算符判断空字符串与 NULL，结果仍然为空。

NOT IN 与空值

对于 WHERE 条件中的 IN 和 NOT IN 运算符，使用的是等值比较。所以如果 NOT IN 碰到了 NULL 值，永远不会返回任何结果。例如：

SELECT employee\_id, first\_name, last\_name, manager\_id
  FROM employees
 WHERE 1 NOT IN (NULL, 2);
复制代码

因为上面的条件实际上等价于：

SELECT employee\_id, first\_name, last\_name, manager\_id
  FROM employees
 WHERE 1 != NULL AND 1 != 2;
复制代码

1 不等于 NULL 的结果是未知，1 不等于 2 的结果是真，未知和真的 AND 运算结果还是未知。

⚠️如果使用 NOT IN，一定要确保括号中的值不会出现 NULL；或者尽量使用 NOT EXISTS。

函数与空值

一般来说，函数和表达式的参数中如果存在 NULL，其结果也是 NULL。当然也有一些例外，比如聚合函数。

以下查询返回的都是 NULL：

SELECT ABS(NULL), 1 + NULL
  FROM employees
 WHERE employee\_id = 100;
ABS(NULL)|1 + NULL|
---------|--------|
   \[NULL\]|  \[NULL\]|
复制代码

一个未知值的绝对值仍然未知，1 加上一个未知值结果还是未知。

但是一个常见的例外是字符串与 NULL 的连接：

— Oracle、SQL Server、PostgreSQL

SELECT CONCAT('Hello', NULL)
  FROM employees
 WHERE employee\_id = 100;
CONCAT('HELLO',NULL)|
--------------------|
Hello               |
复制代码

— MySQL

SELECT CONCAT('Hello', NULL)
  FROM employees
 WHERE employee\_id = 100;
CONCAT('Hello', NULL)|
---------------------|
               \[NULL\]|
复制代码

Oracle 将 NULL 看作空字符串，所以查询结果为 “Hello”；SQL Server 和 PostgreSQL 虽然区分了 NULL 和空字符串，但是 CONCAT 函数中这两者等价；MySQL 中 NULL 参数导致 CONCAT 函数结果为 NULL；SQLite 没有提供 CONCAT 函数。

另外，Oracle 中的 || 也将 NULL 看作空字符串；其他数据库 || 中的 NULL 将参数会产生 NULL 结果；SQL Server 中使用 + 连接字符串，NULL 参数将会产生 NULL 结果。

聚合函数（SUM、COUNT、AVG 等）通常会在进行计算之前删除 NULL 数据：

SELECT SUM(salary + commission\_pct) sum1,
       SUM(salary) + SUM(commission\_pct) sum2,
       COUNT(salary),
       COUNT(commission\_pct)
  FROM employees;
SUM1    |SUM2    |COUNT(SALARY)|COUNT(COMMISSION\_PCT)|
--------|--------|-------------|---------------------|
311507.8|691423.8|          107|                   35|
复制代码

第一个 SUM 函数返回的是 salary 和 commission_pct 都不为空的数据总和；第而个 SUM 函数返回的是 salary 不为空的数据总和加上 commission_pct 不为空的数据总和，所以比第一个数据大；COUNT 函数结果显示 salary 有 107 条记录不为空，commission_pct 只有 35 条记录不为空。

如果输入数据都是 NULL 值，除了 COUNT 函数之外的其他聚合函数返回 NULL：

SELECT COUNT(\*), COUNT(commission\_pct), AVG(commission\_pct), SUM(commission\_pct)
  FROM employees
 WHERE commission\_pct IS NULL;
COUNT(\*)|COUNT(COMMISSION\_PCT)|AVG(COMMISSION\_PCT)|SUM(COMMISSION\_PCT)|
--------|---------------------|-------------------|-------------------|
      72|                    0|             \[NULL\]|             \[NULL\]|
复制代码

COUNT(*) 总是返回数据的行数，不受空值的影响；COUNT(commission_pct) 返回了零；AVG 和 SUM 返回了 NULL。

￥ DISTINCT、GROUP BY、UNION 与空值
SQL 中的分组操作将所有的 NULL 值分到同一个组，包括 DISTINCT、GROUP BY 以及窗口函数中的 PARTITION BY。例如：

SELECT DISTINCT commission\_pct
  FROM employees;
commission\_pct|
--------------|
        \[NULL\]|
          0.40|
          0.30|
          0.20|
          0.25|
          0.15|
          0.35|
          0.10|


SELECT commission\_pct
  FROM employees
 GROUP BY commission\_pct;
commission\_pct|
--------------|
        \[NULL\]|
          0.40|
          0.30|
          0.20|
          0.25|
          0.15|
          0.35|
          0.10|
          
复制代码

从上面的示例可以看出，commission_pct 为空的数据有 72 条，但是分组之后只有一个 NULL 组。

除此之外，UNION 操作符也将所有的 NULL 看作相同值：

SELECT manager\_id
  FROM employees
 WHERE manager\_id IS NULL
 UNION
SELECT manager\_id
  FROM employees
 WHERE manager\_id IS NULL;
manager\_id|
----------|
    \[NULL\]|
    
复制代码

如果将 UNION 换成 UNION ALL，查询结果将会保留 2 个 NULL 值。

ORDER BY 与空值

SQL 标准没有定义 NULL 值的排序顺序，但是为 ORDER BY 定义了 NULLS FIRST 和 NULLS LAST 选项，用于明确指定空值排在其他数据之前或者之后。

不同数据库对此提供了不同的实现：

SELECT employee\_id, manager\_id
  FROM employees
 WHERE employee\_id IN (100, 101, 102)
 ORDER BY manager\_id;
复制代码

— Oracle、PostgreSQL

EMPLOYEE\_ID|MANAGER\_ID|
-----------|----------|
        101|       100|
        102|       100|
        100|    \[NULL\]|
复制代码

— MySQL、SQL Server、SQLite

employee\_id|manager\_id|
-----------|----------|
        100|    \[NULL\]|
        101|       100|
        102|       100|
复制代码

其中，Oracle 和 PostgreSQL 默认将 NULL 作为最大值，升序时排在最后；MySQL、SQL Server 和 SQLite 默认将 NULL 作为最小值，升序时排在最前。

另外，Oracle、PostgreSQL 和 SQLite 提供了扩展的 NULLS FIRST 和 NULLS LAST 选项：

— Oracle、PostgreSQL 和 SQLite

SELECT employee\_id, manager\_id
  FROM employees
 WHERE employee\_id IN (100, 101, 102)
 ORDER BY manager\_id NULLS FIRST;
employee\_id|manager\_id|
-----------|----------|
        100|    \[NULL\]|
        101|       100|
        102|       100|
复制代码

我们也可以使用 CASE 表达式实现类似的效果。以下示例与 NULLS LAST 作用相同，而且所有数据库都可以使用：

SELECT employee\_id, manager\_id
  FROM employees
 WHERE employee\_id IN (100, 101, 102)
 ORDER BY CASE WHEN manager\_id IS NULL THEN 1
               ELSE 0
          END,
          manager\_id;
employee\_id|manager\_id|
-----------|----------|
        101|       100|
        102|       100|
        100|    \[NULL\]|
复制代码