SQLite LEFT JOIN：保留左表的每一行

LEFT JOIN 会保留左边的一切

INNER JOIN 只返回两边都匹配的行。这通常是你想要的，但并不总是。有时“没有匹配”本身就是你要找的答案：还没下过单的用户、从未卖出过的商品、没有评论的帖子。这些情况就需要 LEFT JOIN。

LEFT JOIN 会返回左表的每一行。如果右表有匹配的行，你会得到匹配的列。如果没有，你仍然会得到左表的那一行，右侧的列返回 NULL。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

Cleo 没有订单，但她仍然出现在结果中，total 列是 NULL。把 LEFT JOIN 换成 INNER JOIN，Cleo 就完全消失了。

思维模型

从上往下读这个查询，把左表看作锚点。无论如何，users 中的每一行都会出现在输出中。然后 LEFT JOIN 会针对每个用户问一句：“orders 中有匹配的行吗？”

• 找到匹配 → 把匹配的列拼接到这个用户行上。
• 多个匹配 → 每个匹配各生成一行输出（Ada 有两个订单，所以她出现了两次）。
• 没有匹配 → 生成一行，右表的每一列都是 NULL。

最后这种情况正是 LEFT JOIN 存在的全部理由。这里的 NULL 并不表示“不知道”，而是表示“右边没有东西可以拼接”。

LEFT OUTER JOIN 是同一个操作。在 SQLite 中 OUTER 关键字可以省略，大多数人也都会省略它。

找出没有匹配的行

LEFT JOIN 的经典用法：找出左表中在右表里没有匹配的行。诀窍是在连接之后，检查右表中某个在实际数据里一定有值（NOT NULL）的列（通常是主键）是否为 NULL：

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

只有 Cleo 被返回。连接会在有订单数据的地方把它拼接上；随后 WHERE o.id IS NULL 只保留拼接失败的行。这种写法有时被称为“反连接”（anti-join）。

ON 与 WHERE：一个隐蔽的陷阱

这是 LEFT JOIN 最常见的 bug，值得停下来仔细看看。条件既可以写在 ON 子句中，也可以写在 WHERE 子句中，但在外连接中它们的表现非常不同。

• ON 在连接进行的过程中执行。写在这里的条件决定右表的哪些行算作匹配。
• WHERE 在连接生成结果行之后执行。它过滤的是合并后的结果。

看看把针对右表的条件写在 WHERE 中会发生什么：

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- Wrong: silently turns LEFT JOIN into INNER JOIN
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

Cleo 没有订单，所以她那一行的 o.status 是 NULL，而 NULL = 'shipped' 不为真，她就被过滤掉了。Boris 的状态是 'pending'，也被丢掉了。LEFT JOIN 悄悄地变成了 INNER JOIN。

解决办法：把条件移到 ON 中，这样它过滤的是匹配，而不是输出的行：

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

现在每个用户都出现了。Ada 得到了她已发货的订单；Boris 得到 NULL（他那个待处理的订单不算匹配）；Cleo 得到 NULL（她根本没有订单）。当问题是“列出每个用户，以及他们已发货的订单（如果有的话）”时，这才是正确答案。

经验法则：针对左表的条件可以写在 WHERE 中。针对右表的条件几乎总是应该写在 ON 中，除非你就是想用 IS NULL 找出没有匹配的行。

用 LEFT JOIN 计数

一个常见的任务：统计每个父记录有多少相关行，包括数量为零的父记录。INNER JOIN 会丢掉那些零。LEFT JOIN 加上对右侧某一列的 COUNT 就能得到正确答案：

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

有两点值得注意：

• COUNT(o.id) 统计的是右侧非空的行。Cleo 得到的是 0 而不是 1，因为 COUNT 会忽略 NULL。如果你写的是 COUNT(*)，Cleo 会得到 1（这一行存在，只是里面都是 NULL）。几乎总是应该用 COUNT(right.id)。
• COALESCE(SUM(o.total), 0) 把 Cleo 的 NULL 总和变成 0。不加的话，她的收入会显示为 NULL，技术上没错，但显示出来不好看。

连接多张表

LEFT JOIN 可以串联。每次连接都会拿当前的结果再连接另一张表。一旦你通过 LEFT JOIN 让某一列可能为空，后面挂在它上面的表也要继续用 LEFT JOIN，否则下一个 INNER JOIN 会悄悄丢掉你想保留的行。

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

返回了三个用户。Ada 有订单，也有发货记录。Boris 有订单但没有发货记录（carrier 是 NULL）。Cleo 没有订单，所以 o.total 和 s.carrier 都是 NULL。无论数据在关系链的哪一层断掉，这一串 LEFT JOIN 都会保留每一个用户。

什么时候该用 LEFT JOIN

当问题本质上是关于左表的，而右表只是补充信息时，就用 LEFT JOIN。像“每个用户，以及他们的订单（如果有的话）”或“所有商品及其最新评价”这样的说法，都直接对应 LEFT JOIN。

当两边都同样必需时，就用 INNER JOIN：“订单及其用户信息”对于没有用户的订单毫无意义，所以内连接的过滤效果正是你想要的。

如果你发现自己写了 LEFT JOIN ... WHERE right.col IS NOT NULL，那你其实想要的是 INNER JOIN。如果你写的是 LEFT JOIN ... WHERE right.col IS NULL，那你想要的是反连接，而且写对了。

下一步：自连接

有时你要连接的表，正是你正在查询的同一张表：员工和他们的经理、分类和它们的上级分类、住在同一城市的两两用户。这就是自连接，也是下一页的内容。