第 2.4 课 · 阅读时间：约 6 分钟

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

SQL IS NULL 和 IS NOT NULL

为什么 = NULL 什么也找不到

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

SELECT COUNT(*) AS not_returned FROM rental WHERE return_date = NULL;

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

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

IS NULL

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

SELECT COUNT(*) AS not_returned FROM rental WHERE return_date IS NULL;

结果：183。

SELECT rental_id, rental_date, customer_id
FROM rental
WHERE return_date IS NULL
ORDER BY rental_date
LIMIT 3;

结果：租赁 14098（客户 554）、13421（客户 366）和 15542（客户 111），也就是至今未归还的最早的几部电影。

IS NOT NULL

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

SELECT COUNT(*) AS returned FROM rental WHERE return_date IS NOT NULL;

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

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

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

SELECT
    (SELECT COUNT(*) FROM rental WHERE return_date >= '2005-08-01') AS after_august,
    (SELECT COUNT(*) FROM rental WHERE NOT (return_date >= '2005-08-01')) AS before_august,
    (SELECT COUNT(*) FROM rental) AS total;

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

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

SELECT COUNT(*) AS late_or_unknown
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

结果：8390 = 8207 + 183。

NOT IN 的陷阱

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

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, 3);

结果：1000。

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, NULL);

结果：0，因为对每部电影来说 language_id <> NULL 都是 UNKNOWN。

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

用一个值代替 NULL

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

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

结果：4 条租赁记录；第一条 14098 显示为 "not returned"。

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

本课要点：

• 与 NULL 的任何比较（=、<>、>、<）结果都是 UNKNOWN，WHERE 会丢掉这些行。
• 用 IS NULL 查找没有值的行，用 IS NOT NULL 查找有值的行。
• 一个条件和它的 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。

COUNT(column) 会统计 NULL 值吗？

不会。COUNT(*) 统计行数，COUNT(column) 只统计该列中不为 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 的行会同时被条件和它的否定丢掉。