十五、小心 SQL 中的空值陷阱！

上一篇我们以分析世界银行全球 GDP 和人口数据为案例，回顾了基础篇所学的各种 SQL 基本查询和分析功能。
从本篇开始我们将会学习进阶篇的内容。进阶篇将会介绍更加复杂的多表查询、子查询、集合运算以及各种高级的数据分析技术，能够让我们真正体会到 SQL 数据处理和分析的强大。
作为进阶篇的开始，我们先来讨论一下 SQL 中的空值问题，因为空值的特殊性导致我们很容易出现一些错误和问题。

空值与三值逻辑

SQL 中的空值（NULL）是一个特殊的值，代表了缺失/未知的数据或者不适用的情况。例如，用户在注册时没有提供电子邮箱地址，那么该用户的邮箱就未知；是否怀孕对于男性员工就不适用。
对于大多数的编程语言而言，逻辑运算的结果只有两种情况：真（Ture）或者假（False）。但是对于 SQL 而言，逻辑运算结果存在三种情况：真、假或者未知（Unknown）：

对于 SQL 查询中的 WHERE 条件，只有结果为真的数据才会返回，结果为假或者未知都不会返回。
因此，SQL 中的逻辑运算符 AND、OR 以及 NOT 的结果也存在三种情况。以下是 AND 操作符的逻辑真值表：

AND 操作符只有当两边的结果都为真时，最终结果才为真；否则结果为假或者未知，意味着不返回结果。
下面是 OR 操作符的逻辑真值表：

OR 操作符只要有一个结果为真，最终结果就为真；否则结果为假或者未知，意味着不返回结果。
最后是 OR 操作符的逻辑真值表：

NOT 操作符用于取反操作，对于未知结果取反结果还是未知。
三值逻辑带来的直接问题就是空值的比较运算。

空值比较与运算

任何值与 NULL 值进行比较时，结果无法确认是真还是假。以下比较运算的结果都是未知：

NULL = 0 NULL != 0 NULL > 1 NULL = NULL NULL != NULL

NULL 等于 NULL 的运算结果未知，NULL 不等于 NULL 的运算结果也未知。因此，为了判断某个值是否为空，SQL 提供了 IS NULL 和 IS NOT NULL 谓词：

SELECT emp_id   FROM employee  WHERE NULL = NULL; 
emp_id|
------|
SELECT emp_id   FROM employee  WHERE NULL IS NULL;
emp_id|
------|
1|
2|
3|
...

第一个查询使用等号进行判断，没有返回结果；第二个查询使用 IS NULL，返回了所有的员工编号。
另外，当表达式或者函数涉及 NULL 值时，通常结果也是 NULL 值。例如，以下运算的结果都为空：

SELECT 100 + NULL, UPPER(NULL)   FROM employee  WHERE emp_id = 1;
100 + NULL|UPPER(NULL)|
----------|-----------|
|           |

不过也存在一些例外，比如：

SELECT CONCAT('hello', NULL)   FROM employee  WHERE emp_id = 1;

该查询只有在 MySQL 返回 NULL，CONCAT 函数在 Oracle、SQL Server 以及 PostgreSQL 中将 NULL 当作空字符串处理，返回字符串“hello”。由于不同数据库采取了不同的实现，因此在使用时这些函数时需要小心。

空值的排序

在第 6 篇中我们介绍了 ORDER BY 排序，并且讨论了空值的排序规则。由于 SQL 标准没有提出明确要求，导致在不同的数据库中存在两种空值排序方式：

• Oracle 和 PostgreSQL，升序时空值排在最后，降序时空值排在最前；同时支持使用 NULLS FIRST 和 NULLS LAST 指定空值的顺序；
• MySQL 和 SQL Server，升序时空值排在最前，降序时空值排在最后。

我们可以利用 COALESCE 函数或者 CASE 表达式解决空值的排序问题，第 11 篇给出了 CASE 表达式相关的示例。下面我们介绍一下 SQL 中关于空值处理的函数。

空值函数

SQL 中定义了两个与空值转换相关的函数：NULLIF 和 COALESCE 。

NULLIF

NULLIF(expr1, expr2) 函数接受 2 个参数，如果第一个参数等于第二个参数，返回 NULL；否则，返回第一个参数的值。
以下示例说明了 NULLIF 函数的作用：

SELECT NULLIF(1, 2),        NULLIF(3, 3)   FROM employee  WHERE emp_id = 1;
NULLIF(1, 2)|NULLIF(1, 1)|
------------|------------|
1|      [NULL]|

因为 1 不等于 2，该查询的第一列结果为 1；因为 3 等于 3，第二列结果为 NULL。
NULLIF 函数的一个常见用途是防止除零错误：

-- 除零错误
SELECT *   FROM employee  WHERE 1 / 0 = 1;
-- 避免除零错误 
SELECT *   FROM employee  WHERE 1 / NULLIF(0 , 0) = 1;

第一个查询中被除数为 0，出现除零错误（MySQL 可能不会提示错误）；第二个查询使用 NULLIF 函数将被除数 0 转换为 NULL，整个结果为 NULL。

COALESCE

COALESCE(expr1, expr2, expr3, …) 函数接受一个参数列表，并且返回第一个非空的参数；如果所有参数都为空，返回 NULL。
以下示例用于查询员工的总收入：

SELECT emp_name,
salary*12 + bonus AS "年收入",
salary*12 + COALESCE(bonus, 0) AS "年收入"
FROM employee  WHERE emp_id <= 6;
emp_name|年收入     |年收入    |
--------|---------|---------|
刘备      |370000.00|370000.00|
关羽      |322000.00|322000.00|
张飞      |298000.00|298000.00|
诸葛亮    |296000.00|296000.00|
黄忠      |   [NULL]| 96000.00|
魏延      |   [NULL]| 90000.00|

该查询中的第三列利用 COALESCE 函数将奖金为空的数据转换为 0，得到了正确的年收入。

专有函数

除了SQL 标准中定义的表达式之外，许多数据库还实现了一些类似的扩展函数：

• Oracle 提供了 NVL(expr1, expr2) 以及 NVL2(expr1, expr2, expr3) 函数；
• MySQL 提供了 IFNULL(expr1, expr2) 以及 IF(expr1, expr2, expr3) 函数；
• SQL Server 提供了 ISNULL(expr1, expr2) 函数。

首先来看 NVL(expr1, expr2) 函数，它用于返回第一个非空的参数值，相当于只有两个参数的 COALESCE 函数。以下查询也返回员工的总收入：

-- Oracle 实现
SELECT emp_name,
salary*12 + NVL(bonus, 0) AS "年收入"
FROM employee  WHERE emp_id <= 6;

该查询的结果与上一节 COALESCE 函数示例相同。
接下来是 NVL2(expr1, expr2, expr3) 函数，它包含三个参数。如果第一个参数不为空，返回第二个参数的值；否则，返回第三个参数的值。以下查询同样返回员工的总收入：

-- Oracle 实现
SELECT emp_name,
NVL2(bonus, salary*12 + bonus, salary*12) AS "年收入"
FROM employee  WHERE emp_id <= 6;

该查询的结果与上一节 COALESCE 函数示例相同。
MySQL 提供了 IFNULL(expr1, expr2) 函数。该函数用于返回第一个非空的参数值，相当于只有两个参数的 COALESCE 函数。以下查询也返回员工的总收入：

-- MySQL 实现
SELECT emp_name,
salary*12 + IFNULL(bonus, 0) AS "年收入"
FROM employee  WHERE emp_id <= 6;

该查询的结果与上一节 COALESCE 函数示例相同。
MySQL 提供了类似于 NVL2 的 IF(expr1, expr2, expr3) 函数。它接受三个参数，如果第一个参数为真（expr1 <> 0 并且 expr1 不为空），返回第二个参数的值；否则，返回第三个参数的值。以下查询同样返回员工的总收入：

-- MySQL 实现
SELECT emp_name,
IF(bonus, salary*12 + bonus, salary*12) AS "年收入"
FROM employee  WHERE emp_id <= 6;

该查询的结果与上一节 COALESCE 函数示例相同。
SQL Server 提供了 ISNULL (expr1, expr2) 函数。该函数用于返回第一个非空的参数值，相当于只有两个参数的 COALESCE 函数。以下查询也返回员工的总收入：

-- SQL Server 实现
SELECT emp_name,
salary*12 + ISNULL(bonus, 0) AS "年收入"
FROM employee  WHERE emp_id <= 6;

该查询的结果与上一节 COALESCE 函数示例相同。

分组与聚合函数中的空值

GROUP BY 子句、DISTINCT 运算符将所有的空值视为相同，因此将它们分为一组。虽然许多员工没有奖金，以下查询的结果中只有一个 NULL 值：

SELECT DISTINCT bonus   FROM employee;
SELECT bonus   FROM employee  GROUP BY bonus;

该查询的结果如下：

bonus   |
--------|
10000.00|
8000.00|
[NULL]|
5000.00|
6000.00| 
2000.00| 
1500.00|

另外，在第 12 篇中我们介绍了常见的聚合函数（AVG、SUM、COUNT 等），聚合函数通常会忽略空值。

NOT IN 中的空值

IN 操作符用于查询位于列表之中的数据，NOT IN 用于查询不位于列表中的数据。以下是一个 IN 操作符的示例：
SELECT empid FROM employee WHERE emp_id IN (1, 2, 3, NULL); emp_id| ———| 1| 2| 3| 复制
该查询的结果返回了 3 条记录，因为他们的员工编号位于列表之中。接下来使用 NOT IN 查询不在列表中的数据：
SELECT emp_id FROM employee WHERE emp_id NOT IN (1, 2, 3, NULL); emp_id| ———|_ 复制
该查询没有返回任何结果。原因在于 IN 操作符使用等号（=）依次与列表中的数据进行比较，该查询等价于以下形式：
SELECT emp_id FROM employee WHERE emp_id != 1 AND emp_id != 2 AND emp_id != 3 AND emp_id != NULL; 复制
任何值与 NULL 比较的结果都是未知；因此没有数据满足该条件，也就没有返回结果。
为了避免各种情况下空值可能带来的问题，可以利用 SQL 提供的空值处理函数将 NULL 值转换为其他数据。

小结

空值（NULL）是 SQL 中容易被忽略的地方，经常会导致一些不可预知的错误结果。需要注意 SQL 在各个子句和函数中对于空值的处理方式。一个比较通用的解决方法就是利用 COALESCE 函数或者 CASE 表达式将这些空值转换成确定的数据。
练习题：假设下面的 emp_contact 表中存储的是员工的联系电话，包括工作电话、移动电话、家庭电话以及紧急联系人电话：
CREATE TABLE emp_contact( emp_id INT NOT NULL PRIMARY KEY, work_phone VARCHAR(20), mobile_phone VARCHAR(20), home_phone VARCHAR(20), emergency_phone VARCHAR(20) ); INSERT INTO emp_contact VALUES (1, ‘010-61231111’, NULL, NULL, NULL); INSERT INTO emp_contact VALUES (2, NULL, ‘13222222222’, ‘010-61232222’, ‘13123456789’); INSERT INTO emp_contact VALUES (3, NULL, NULL, NULL, ‘13123450000’); INSERT INTO emp_contact VALUES (4, NULL, NULL, ‘010-61234444’, ‘13123457777’); 复制
现在需要找出每个员工的联系电话，规则是先找移动电话；如果没有，再找工作电话；如果没有，再找家庭电话；最后找紧急联系人电话。试试你可以写出几种查询方法？