SQLite NULL 处理：IS NULL、COALESCE 与 IFNULL

NULL 表示“未知”

SQLite 中的其他值都代表某种确定的东西：一个数字、一个字符串、一个 blob。NULL 不一样，它是一个占位符，代表缺失或未知的值。NULL 在查询中的种种怪异表现，都可以用这一个概念解释。

先建一张小表来练手：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

有两列允许为 null。Boris 没有邮箱，Cleo 没有年龄，Dan 两者都没有。这一页剩下的内容，讲的就是如何查询这样的行而不被坑到。

= 和 <> 对 NULL 不起作用

你的第一反应是写 WHERE email = NULL。看起来很合理，结果却什么都没返回：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

零行，尽管 Boris 和 Dan 的邮箱明明是 null。原因是：任何值与 NULL 比较，结果都是 NULL，既不是真也不是假。SQLite 的 WHERE 子句只保留条件为真的行，而 NULL 不是真，所以这些行被过滤掉了。

<> 也有同样的陷阱：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

你以为这会返回除 Ada 以外的所有人，结果只返回了 Cleo。邮箱为 null 的 Boris 和 Dan 被丢掉了，因为 NULL <> 'ada@example.com' 的结果同样是 NULL，而不是真。

这是 SQL 中最常见的一个坑。只要查询莫名其妙地“丢了行”，就先怀疑是不是某一列有 null。

使用 IS NULL 和 IS NOT NULL

判断 null 的正确方式是 IS 运算符。和 = 不同，它认得 null，只会返回真或假，绝不会返回 null：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

第一个查询返回 Boris 和 Dan，第二个返回 Ada 和 Cleo。IS NULL 和 IS NOT NULL 这两个运算符就是专门用来问“这个值缺失了吗？”的。凡是你想写 = NULL 或 <> NULL 的地方，都改用它们。

如果你想要“不是 Ada，也包括那些未知的”，就把两个条件明确地组合起来：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

现在 Boris、Cleo 和 Dan 都出现了。

NULL 会在算术运算和字符串拼接中传播

“未知”这条规则不只适用于比较。任何碰到 null 的运算，结果都是 null：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1       AS next_year,
    age * 2       AS doubled,
    'Hi ' || name AS greeting,
    'Age: ' || age AS labelled_age
FROM users;

对 Cleo 和 Dan 来说，next_year 和 doubled 都是 null。他们的 labelled_age 也是 null：字符串与 NULL 拼接得到的是 NULL，而不是 'Age: '。如果一列可能为 null，而你又需要一个可用的结果，就必须处理它。这正是接下来两个函数的用武之地。

IFNULL：两个参数的兜底

IFNULL(a, b) 在 a 不为 null 时返回 a，否则返回 b。这是把 null 替换成默认值最简单的方法：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(no email)') AS email,
    IFNULL(age, 0)              AS age
FROM users;

Boris 和 Dan 得到 (no email)，Cleo 和 Dan 得到 0。原始数据并没有被修改，IFNULL 只是改写了输出。

IFNULL 永远只接受两个参数。如果需要更多兜底值，就用 COALESCE。

COALESCE：第一个非 NULL 值胜出

COALESCE(a, b, c, ...) 从左到右依次检查参数，返回第一个不为 null 的值。它把 IFNULL 推广到了任意数量的兜底值：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@unknown.local', 'anonymous') AS contact
FROM users;

Ada 和 Cleo 用的是邮箱。Boris 和 Dan 的邮箱是 null，于是 SQLite 尝试第二个参数，也就是根据名字拼出来的地址。如果这个也是 null，就会落到 'anonymous'。

COALESCE 是可移植的选择，所有主流 SQL 数据库对它的支持都一样。IFNULL 是 SQLite 和 MySQL 为两个参数的情况提供的便捷写法。默认选 COALESCE；只有当你确实只有两个参数、又想要更短的名字时，才用 IFNULL。

NULL 不是空字符串

一个常见的误解：把 NULL 和 '' 当成可以互换的。其实不行。

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' 是一个真实的字符串，只不过字符数为零。NULL 则是值的缺失。length('') 是 0；length(NULL) 本身就是 NULL。而 NULL = NULL 是 NULL，不是 1，这正是 IS NULL 存在的原因。

如果一列既可能是 '' 也可能是 NULL，就决定其中哪个代表“缺失”，并坚持下去。两者混用会迫使每个查询都处理两种情况，而你迟早会漏掉一种。

IN、NOT IN 和 DISTINCT 中的 NULL

还有几个地方，null 会悄悄给你制造麻烦。

IN 的列表里如果有 null，结果可能出人意料，尤其是 NOT IN：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

你可能以为会返回所有年龄不是 25 的人，结果什么都没有。SQLite 会把 NOT IN (25, NULL) 大致展开成 age <> 25 AND age <> NULL，而 age <> NULL 永远是 NULL，所以整个条件永远不会为真。解决办法是在比较之前，先把 null 从列表（或列）中过滤掉。

另一方面，DISTINCT 在去重时会把所有 null 视为相等：

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

你会得到三行：Ada 的邮箱、Cleo 的邮箱，以及一个 NULL（由 Boris 和 Dan 的合并而来）。GROUP BY 和 UNION 也一样，它们把所有 null 当作同一组，这和 = 对待 null 的方式恰好相反。SQL 在这件事上并不总是一致，弄清每个运算符站在哪一边是值得的。

快速检查清单

• 用 IS NULL / IS NOT NULL 判断缺失值，绝不要用 = NULL。
• 任何碰到 NULL 的算术运算、拼接或比较都会返回 NULL。
• 用 COALESCE(a, b, c, ...) 把 null 替换成兜底值。两个参数时可以用简写 IFNULL(a, b)。
• 空字符串 '' 和 NULL 不是一回事。每一列都只选一种来表示“缺失”。
• NOT IN (..., NULL) 几乎总是 bug。先把 null 从列表里去掉。

下一篇：结果排序

一旦你能正确地过滤行（包括那些含 null 的行），下一步就是把它们排成有用的顺序。下一页讲 ORDER BY，它对 null 在排序结果中的位置也有自己的规则。