查询运行时没有错误并返回看起来合理的数字，但缺少行且平均值错误。 SQL NULL 值 通常是原因。 NULL 的行为与任何正常值不同，这让初学者和经验丰富的开发人员都感到困惑。

本指南准确展示了 NULL 的行为方式。我们在 SQLite 3.50 中运行每个查询，显示的结果来自该运行。大多数这种行为是标准 SQL，我们会注意到数据库的不同之处，例如 NULL 排序顺序以及 Oracle 对空字符串的处理。

什么是NULL？

NULL 是一个标记，表示值丢失或未知。它不是通常意义上的值，因此这些都是不同的东西：

这种差异对于金额尤其重要。奖金为 0 意味着该人什么也没收到。空奖金意味着还没有人知道。

样本表

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

请注意，Sara 的电话号码为空字符串，而 Arjun 和 Ravi 的电话号码为 NULL。两者的待遇截然不同。

测试 NULL

这是几乎每个人一开始都会犯的错误：

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

第一个查询是有效的 SQL 并且没有给出错误。它只是不返回任何内容，因为将任何内容与 NULL 进行比较都不会成立。 Sara 显示在 IS NOT NULL 下，因为空字符串是实值。但在 Oracle 中，空字符串被视为 NULL，这是一个值得注意的例外。

三值逻辑

在大多数编程语言中，条件是 true 或 false。 SQL 添加第三个结果：未知。如果您不知道某个值，则无法判断它是否等于 5。并且不知道两个未知值是否相等，这就是 NULL = NULL 不成立的原因。在我们的测试中，所有这些都返回 NULL：

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

组合条件遵循仔细的规则。以下是我们的运行结果，其中 1 为 true，0 为 false：

从 WHERE 消失的行

WHERE 仅当条件为 true 时才保留行。结果未知的行以及错误的行都会被删除。这导致了令人惊讶的结果：

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

您可能会期待阿琼和拉维出现，因为他们的电话肯定不是那个号码。但他们的电话不详，所以结果未知，他们就消失了。要包含它们，请明确说明：WHERE phone <> '555-0101' OR phone IS NULL。否定也无济于事：

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara和Ravi没有奖金，所以他们既没有出现在bonus > 400中，也没有出现在它的对立面中。这是要记住的关键一点：条件及其否定并不总是覆盖每一行。

COALESCE：提供后备

COALESCE 返回其列表中的第一个非 NULL 值，并且它适用于所有主要数据库：

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara 仍然显示为空白，因为空字符串不为 NULL。类似的功能也存在，例如 MySQL 和 SQLite 中的 IFNULL、SQL Server 中的 ISNULL 以及 Oracle 中的 NVL。 COALESCE 是便携式的选择。我们之前关于计算的观点也适用于此。 bonus + 100 对于没有奖金的任何人都给出 NULL，因此如果将缺失的奖金视为零对于您的业务来说是正确的，请填写 COALESCE(bonus, 0) + 100。

NULLIF：将一个值变成NULL

如果两个值相等，NULLIF(a, b) 返回 NULL，否则返回 a。有两种用途特别有用。它可以清理占位符值：

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

第二个查询是查找“缺失或空白”值的便捷方法。它还可以防止被零除，这会在许多数据库中引发错误：

SELECT total / NULLIF(quantity, 0) FROM sales;

当 quantity 为 0 时，这会给出 NULL 而不是失败。在我们的测试中，SELECT 100 / NULLIF(0, 0) 返回 NULL。

聚合跳过 NULL

这是 NULL 对报告造成最大损害的地方：

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

那么平均奖金是多少，400还是200？这取决于 NULL 在数据中的含义。如果 NULL 表示“没有支付奖金”，那么 200 是正确的。如果它的意思是“尚未决定”，那么400可能是正确的。数据库无法为你做出这个决定，所以这个问题必须交给了解业务的人。

一个相关的陷阱：没有值的 SUM 是 NULL 而不是 0。当我们只对没有奖励的行求和时，我们得到 NULL，而 COUNT 返回 0。期望数字的应用程序代码可能会崩溃，所以将其包装：COALESCE(SUM(bonus), 0)。

GROUP BY、排序和 DISTINCT

尽管 NULL 在比较中“不相等”，但这些操作将它们视为一组：

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

有关分组的更多信息，请参阅 SQL GROUP BY 和 HAVING。排序顺序是数据库真正不同的地方。 SQLite和MySQL将NULL视为最小值，而PostgreSQL和Oracle将其视为最大。因此，相同的 ORDER BY bonus DESC 可以将 NULL 放在一个中的最后，而将 NULL 放在另一个中的第一个。 PostgreSQL、Oracle 和现代 SQLite 允许您使用 NULLS FIRST 或 NULLS LAST 直接说明这一点。

加入

使用 = 的连接条件永远不会匹配 NULL，因此具有 NULL 键的行会从内部连接中删除：

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera 没有经理，因此内部联接会默默地删除该行。 A LEFT JOIN 保留它，并且它还为缺失的一侧创建自己的 NULL。请阅读 SQL 连接解释 来了解更多信息。

NOT IN 陷阱

NOT IN 列表中的单个 NULL 会清除所有结果：

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

第二种形式意味着manager_id <> 1 AND manager_id <> NULL，而最后一部分永远不可能是真的。这对于子查询尤其危险，因为 NULL 可能会在不被注意的情况下到达。请改用 NOT EXISTS，如我们的 SQL 子查询教程 中所述。第一个结果中也缺少 Meera，因为该行的 manager_id 为 NULL。

使用 NULL 进行设计

• 默认使用NOT NULL。 仅当值确实可能缺失时才允许 NULL。
• 添加合理的默认值。例如，quantity INT NOT NULL DEFAULT 0。
• 请勿使用魔法值。 日期 1900-01-01 或代表“未知”的年龄 -1 会破坏您的计算。
• 了解 UNIQUE 行为。 大多数数据库允许在 UNIQUE 列中存在多个 NULL，并且我们的测试插入了两个 NULL，没有错误。 SQL Server 是一个例外，因为它通常允许这样做。
• 请记住主键不能为 NULL。 请参阅主键与外键。
• 使用 NULL 进行测试。 不使用 NULL 的测试数据隐藏了这些错误。有意将它们包括在内，就像其他边缘情况一样。

最后的话

将 NULL 视为“未知”，它的大部分行为开始变得有意义：未知加一是未知，并且未知等于未知不知道是否为真。本周审核你自己的一份报告。使用 COUNT(*) 对照 COUNT(column) 检查可空列，查找任何 NOT IN，并确定每个平均值中 NULL 的含义。

常见问题

继续学习