SQL IS NULL 和 IS NOT NULL

Q: 为什么 WHERE column = NULL 不返回任何行？

与 NULL 比较的结果是 UNKNOWN，而不是 TRUE，而 WHERE 只保留 TRUE 的行。请使用 WHERE column IS NULL。

Q: IS NOT NULL 和 NOT (column = NULL) 一样吗？

不一样。column = NULL 是 UNKNOWN，NOT UNKNOWN 也是 UNKNOWN，所以 NOT (column = NULL) 同样什么也找不到。IS NOT NULL 对每一个有值的行都是 TRUE。

Q: IS NULL 可以与 AND 和 OR 组合吗？

可以，和其他条件一样：WHERE customer_id = 554 AND return_date IS NULL 能找到客户仍持有的电影，而 OR return_date IS NULL 会把没有值的行加到另一个条件的结果中。

第 2.4 课 · 阅读时间：约 6 分钟 · 练习题 ↓

IS NULL 和 IS NOT NULL 是 SQL 中筛选缺失值的唯一可靠方式。本课你会看到为什么 WHERE column = NULL 什么也找不到，如何找出有值和没有值的行，以及 NULL 如何在使用 NOT、<> 和 NOT IN 的条件中悄悄丢掉行。示例使用 Sakila 数据库的 rental 表：它有 16044 行，对于还没有归还的电影，return_date 为 NULL。

与 NULL 比较的结果是 UNKNOWN，而 WHERE 只保留结果为 TRUE 的行，所以 = NULL 什么也找不到，IS NULL 能找到没有值的行

为什么 `= NULL` 什么也找不到

NULL 表示"值未知"。与未知值的任何比较结果也是未知的：return_date = NULL 既不是真也不是假，而是 UNKNOWN。WHERE 只保留条件为 TRUE 的行，所以查询什么也不返回：

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date = NULL;

结果：没有任何行，尽管有 183 条租赁记录没有归还日期。

<>、>、< 和其他比较运算符也是如此。

IS NULL

IS NULL 是一种特殊的检查，在值缺失时为 TRUE：

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NULL;

结果：183 行，也就是电影仍未归还的租赁记录。

IS NULL 可以像任何比较一样与其他条件组合：

SELECT rental_id, rental_date, return_date
FROM rental
WHERE customer_id = 554 AND return_date IS NULL;

结果：一行，即 2005-08-21 的租赁 14098：客户 554 仍然持有这部电影。

IS NOT NULL

IS NOT NULL 正好相反：当列有值时为 TRUE。

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NOT NULL;

结果：15861 行。加上 183 条没有归还日期的记录，正好是表中全部 16044 行。

NULL 既不满足条件，也不满足它的 NOT

人们很自然地认为，一个条件和它的 NOT 会把表分成两部分。有了 NULL，情况并非如此。比较下面两个查询：

SELECT rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01';

结果：8207 行。

SELECT rental_id, return_date
FROM rental
WHERE NOT (return_date >= '2005-08-01');

结果：7654 行。8207 + 7654 = 15861，而不是 16044：对于 183 条没有归还日期的行，两个条件都是 UNKNOWN，而 NOT UNKNOWN 仍然是 UNKNOWN。

要保留这些行，请用 OR ... IS NULL 明确地把它们加进来：

SELECT rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

结果：8390 行 = 8207 + 183。

NOT IN 的陷阱

NOT IN 是一串用 AND 连接的 <> 比较。如果列表中有 NULL，其中一个比较总是 UNKNOWN，任何行都无法通过：

SELECT title, language_id
FROM film
WHERE language_id NOT IN (2, 3);

结果：1000 行，也就是全部电影。

SELECT title, language_id
FROM film
WHERE language_id NOT IN (2, NULL);

结果：没有任何行，因为对每部电影来说 language_id <> NULL 都是 UNKNOWN。

没有人会在列表里手写 NULL，但在后面的课程中，列表会来自某一列，那里只要有一个 NULL 就足以让结果为空。使用 NOT IN 之前，请确认这些值不可能是 NULL。

用一个值代替 NULL

COALESCE 返回它的参数中第一个不为 NULL 的值。用它可以在结果中显示一个易读的值：

SELECT rental_id, rental_date, COALESCE(return_date, 'not returned')
FROM rental
WHERE customer_id = 554 AND rental_date >= '2005-08-21';

结果：4 条租赁记录；租赁 14098 显示为 "not returned"。没有命名时，该列的标题就是表达式本身。

这是 MySQL 的写法。PostgreSQL 和 SQL Server 要求两个参数的类型相同，所以在那里要先把日期转换成文本。COALESCE 和其他函数会在函数模块中讲解。

本课要点：

与 NULL 的任何比较（=、<>、>、<）结果都是 UNKNOWN，WHERE 会丢掉这些行。
用 IS NULL 查找没有值的行，用 IS NOT NULL 查找有值的行；两者都可以与 AND 和 OR 组合。
一个条件和它的 NOT 合起来也覆盖不到该列为 NULL 的行；要保留它们，请加上 OR column IS NULL。
如果列表中有 NULL，NOT IN 不会返回任何行。
COALESCE(column, value) 会显示一个替代值来代替 NULL。

常见问题

为什么 WHERE column = NULL 不返回任何行？

与 NULL 比较的结果是 UNKNOWN，而不是 TRUE，而 WHERE 只保留 TRUE 的行。请使用 WHERE column IS NULL。

IS NOT NULL 和 NOT (column = NULL) 一样吗？

不一样。column = NULL 是 UNKNOWN，NOT UNKNOWN 也是 UNKNOWN，所以 NOT (column = NULL) 同样什么也找不到。IS NOT NULL 对每一个有值的行都是 TRUE。

IS NULL 可以与 AND 和 OR 组合吗？

可以，和其他条件一样：WHERE customer_id = 554 AND return_date IS NULL 能找到客户仍持有的电影，而 OR return_date IS NULL 会把没有值的行加到另一个条件的结果中。

面试题

为什么 SQL 查询用 IS NULL 而不是 = NULL？

NULL 表示未知的值，把任何东西和未知值比较，结果都是 UNKNOWN，而不是 TRUE 或 FALSE。WHERE 只保留条件为 TRUE 的行，所以 = NULL 永远匹配不到。IS NULL 是一种单独的检查，返回 TRUE 或 FALSE。

为什么 NOT IN 可能返回空结果？

x NOT IN (a, b) 的意思是 x <> a AND x <> b。如果列表中有 NULL，x <> NULL 对每一行都是 UNKNOWN，整个 AND 不可能为 TRUE，于是不返回任何行。当列表来自一个子查询、而该子查询的列中有 NULL 值时，就会出现这种情况。

如何在筛选中保留含 NULL 的行？

明确地加上 NULL 的情况：WHERE return_date >= '2005-08-01' OR return_date IS NULL。否则，含 NULL 的行会同时被条件和它的否定丢掉。