时间：2021-07-01 10:21:17 帮助过：3人阅读
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，结果仍然为空。
对于 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。
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 值。
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]|
首先，CASE 表达式将 manager_id 为空的数据转换为 1，非空的数据转换为 0，所以空值排在其他数据之后；第二个排序字段 manager_id 确保了非空的数据从小到大排序。
由于空值的特殊性，我们在分析数据时经常需要进行空值和其他值的转换。为此，SQL 提供了两个标准的空值函数：COALESCE 和 NULLIF。
COALESCE(exp1, exp2, …) 函数用于将 NULL 转换为其他值。当 exp1 不为空时返回 exp1，否则检查 exp2；如果 exp2 不为空时返回 exp2，依次类推。例如：
SELECT COALESCE(NULL, NULL, 3)
那些年我们踩过的坑，SQL 中的空值陷阱！标签：head sdn block union pre 存储 概念 res comm