9 空值（NULL）

NULL 是 SQL 中表示未知或缺失的标记。它不是零，不是空字符串，也不等于任何值，甚至不等于另一个 NULL。这种三值逻辑（真、假、未知）引发的 SQL 错误比其他任何特性都多，不过一旦熟悉，规则其实很简单。

本章要点

• NULL 表示什么
• 用 IS NULL / IS NOT NULL 检查 NULL
• NULL 如何在表达式和比较中传播
• NULL 在 AND/OR 中的行为（三值逻辑）
• 用 COALESCE 和 NULLIF 替换 NULL（译注：此处原文概括不够准确；前者用非空值替换空值，后者可将指定值转为空值。）

示例

-- NULL is missing/unknown
SELECT 1 AS a, NULL AS b;

-- = NULL is NULL (not true), so this returns 0 rows
SELECT * FROM (VALUES (1, NULL)) AS t(id, name) WHERE name = NULL;

-- IS NULL / IS NOT NULL are the correct tests
SELECT * FROM (VALUES (1, NULL)) AS t(id, name) WHERE name IS NULL;
SELECT * FROM (VALUES (1, 'x')) AS t(id, name) WHERE name IS NOT NULL;

-- Any arithmetic with NULL is NULL
SELECT 1 + NULL AS one_plus_null, 'a' || NULL AS concat_null;

-- COALESCE returns the first non-null argument
SELECT COALESCE(NULL, NULL, 'default') AS coalesced;

-- NULLIF returns NULL if the two args are equal, else the first
SELECT NULLIF(0, 0) AS hides_zero, NULLIF(5, 0) AS keeps_five;

-- Three-valued logic
SELECT (TRUE AND NULL) AS t_and_n, (TRUE OR NULL) AS t_or_n, (FALSE AND NULL) AS f_and_n;

= NULL 不起作用，是因为比较结果是 NULL，不是 TRUE，所以 WHERE 会排除这一行。<>、<、>、IN、BETWEEN 等也是如此；几乎所有操作符都会在任一操作数为 NULL 时返回 NULL。例外是 IS NULL、IS NOT NULL、IS DISTINCT FROM 和 IS NOT DISTINCT FROM（后两者分别是能正确处理 NULL 的 <> 和 =）。

COALESCE(a, b, c) 返回第一个不是 NULL 的参数。这是提供默认值的标准做法，例如 COALESCE(nickname, first_name, 'Anonymous')。NULLIF(a, b) 在 a = b 时返回 NULL；如果想把用作标记的特殊值转回 NULL，它就很方便（例如 NULLIF(comment, '')）。

三值逻辑表很短，值得记住：TRUE OR NULL 是 TRUE（至少有一侧为真），但 TRUE AND NULL 是 NULL（不知道另一侧的值）。FALSE AND NULL 是 FALSE（有一侧为假就够了）。FALSE OR NULL 是 NULL。

运行示例

运行方式：

$ psql -f source/nulls.sql postgres
 a | b
---+---
 1 |
(1 row)
 ...

常见陷阱

• 忘记聚合会跳过 NULL：SUM、AVG、MIN、MAX、COUNT(col) 都忽略 NULL 输入。COUNT(*) 则统计每一行，包括含有 NULL 的行。
• 只要子查询返回一个 NULL，NOT IN (subquery) 就会得到空结果，因为 x <> NULL 的结果是 NULL。优先使用 NOT EXISTS。
• NULL <> NULL 是未知（NULL），不是真。DISTINCT 把 NULL 归为一组；IS DISTINCT FROM 提供的比较则总是返回真或假。
• 默认排序：ORDER BY col ASC 把 NULL 排在最后，DESC 把它们排在最前。明确指定 NULLS FIRST/NULLS LAST，让结果符合预期。

下载

SQL 文件： nulls.sql · 全部示例文件 · 准备工作