Kodokon kodokon.com

LEFT JOIN 与 NULL：找出缺失的东西

用 LEFT JOIN 保留一张表的每一行，用 IS NULL 发现缺失，并用 COALESCE 替换它们。

8 分钟 · 3 题

在 Kodokon 中打开本课

上一课的 INNER JOIN 会隐藏没有订单的客户。然而，最常见的业务问题恰恰是：谁缺失了？ 不活跃的客户、从未售出的产品、没有付款的发票…… 请在 sqliteonline.com 或 sqlite3 中运行这段脚本：它会新增第四个没有订单的客户。

SQL
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Alice', 'Lyon'),
  (2, 'Karim', 'Paris'),
  (3, 'Mina', 'Nantes'),
  (4, 'Paulo', 'Lille');

INSERT INTO orders (id, customer_id, amount)
VALUES
  (1, 1, 49.90),
  (2, 1, 15.00),
  (3, 2, 120.50);
Mina 和 Paulo 没有下过任何订单。

LEFT JOIN 会保留左表（写在关键字之前的那张表）的所有行，即使右侧没有匹配。当匹配缺失时，右表的各列会被填上 NULL。

SQL
SELECT c.name, o.id AS order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id;
Mina 和 Paulo 会出现，其 order_id 和 amount 都为 NULL。

为了单独找出缺失的部分，就对这些 NULL 值进行过滤：这就是反连接（anti-join）模式，是数据分析中一个必备的本能反应。被检测的列必须来自右表，并且本身绝不应自然为 NULL，因此选用它的主键 o.id。

SQL
SELECT c.name, c.city
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.id IS NULL;
从未下过订单的客户：Mina 和 Paulo。

最后一个工具：COALESCE(a, b) 在 a 不为 NULL 时返回 a，否则返回 b。把它与 LEFT JOIN 和聚合结合起来，就能在报表或发票中把缺失转化为可用的零。

SQL
SELECT c.name,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id
ORDER BY total_spent DESC;
每位客户的消费总额，对不活跃的客户用 0 代替 NULL。

知识检测

确认你已牢记本课的重点内容。

  1. 哪种组合能列出从未下过订单的客户？
    • INNER JOIN 然后 WHERE o.id IS NULL
    • LEFT JOIN 然后 WHERE o.id IS NULL
    • LEFT JOIN 然后 WHERE o.id = NULL
  2. 条件 amount = NULL 会返回什么？
    • 对 amount 为 NULL 的行返回真
    • 永远不为真：该比较始终未定义
    • 一个语法错误
  3. 对于一个没有订单的客户，COALESCE(SUM(o.amount), 0) 会返回什么？
    • 0
    • NULL
    • 该客户被排除在结果之外
    • 一个聚合错误