LEFT JOIN 为什么被 WHERE 变成了内连接

排查一张“用户及其已支付订单”的报表时，很容易写出下面的查询：左边放用户表，右边放订单表，使用 LEFT JOIN，再在 WHERE 中筛选已支付订单。看起来保留了所有用户，实际结果却少了人。

问题出在筛选发生的阶段。本文用四名用户、五笔订单复现这个现象，并检验一个常见补救办法：加上 OR o.id IS NULL，是否就能保留所有用户？

一、准备能暴露问题的数据

以下代码可在 SQLite 中执行。订单主键明确设置为非空；订单关联键和状态允许 NULL。这里不设置外键，是为了保留一条孤立订单作为连接边界样例。

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY NOT NULL,
  user_id INTEGER,
  status TEXT
);
INSERT INTO users VALUES
  (1, '小林'), (2, '小周'), (3, '小陈'), (4, '小吴');
INSERT INTO orders VALUES
  (101, 1, 'paid'),
  (102, 1, 'unpaid'),
  (103, 2, 'unpaid'),
  (104, 4, NULL),
  (105, 99, 'paid');

四个人分别覆盖四种情况：小林既有已支付订单，也有未支付订单；小周只有未支付订单；小陈没有订单；小吴有订单，但状态未知。105 号订单找不到对应用户。

先只按用户 ID 连接：

SELECT u.id, u.name, o.id AS order_id, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.id;

结果有五行。小林出现两次，小周和小吴各出现一次，小陈得到一行右侧列全为 NULL 的补齐行。孤立订单 105 不会出现在结果中，因为它没有左侧用户。

LEFT JOIN 保证的是：连接阶段每个左表行至少得到一行。它既不保证最终结果仍包含每个用户，也不保证每个用户只出现一次。

二、WHERE 为什么让人消失

在连接后加上右表筛选：

SELECT u.id, u.name, o.id AS order_id, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY u.id, o.id;

结果只剩小林的 101 号订单。

小周的状态是 unpaid，不满足条件。小吴的状态是 NULL，比较 NULL = 'paid' 得到未知。小陈虽然在连接阶段获得了补齐行，但补齐行里的 status 也是 NULL，同样不会通过 WHERE。

理解结果时，可以按“先连接并补齐，再筛选”推导。这里描述的是查询语义，不是承诺数据库一定按这个物理顺序执行。优化器可以改写执行计划，只要最终结果保持一致。

这一条查询与下面的内连接得到相同结果：

SELECT u.id, u.name, o.id AS order_id, o.status
FROM users u
INNER JOIN orders o
  ON u.id = o.user_id AND o.status = 'paid'
ORDER BY u.id, o.id;

标题里的“变成内连接”说的就是这种结果等价，原因是当前 WHERE 条件会排除右侧补齐的 NULL 行。不能把它推广成“LEFT JOIN 后面只要出现 WHERE，就一定等价于 INNER JOIN”。筛选左表字段、判断右表字段 IS NULL，都会产生不同语义。

三、把筛选放进 ON，保留所有用户

如果需求是“列出所有用户，关联各自已支付的订单；没有已支付订单也要展示”，筛选应参与匹配条件：

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

这次得到四行：小林关联 101；小周、小陈和小吴各得到一行右侧为 NULL 的补齐结果。

小周明明有订单，为什么还能获得补齐行？因为 ON 此时定义的“匹配”是“用户 ID 相同，并且已经支付”。103 号订单在这个定义下不匹配。小周找不到满足整个 ON 条件的右表行，因此需要补齐。

这也解释了两个写法的业务差异。ON 里的条件控制哪些订单可以关联到用户；WHERE 里的条件决定连接后的哪些行进入最终结果。应先明确报表要保留谁，再选择条件位置。

四、OR IS NULL 为什么只修好一半

有人会保留 WHERE，并增加一个空值分支：

SELECT u.id, u.name, o.id AS order_id, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid' OR o.id IS NULL
ORDER BY u.id, o.id;

结果是小林和小陈，只有两行。小周仍然不见了。

这条语句的 ON 只检查用户 ID。小周已经与 103 号订单匹配成功，所以连接阶段没有再为他额外生成补齐行。随后 103 既不是已支付订单，订单 ID 也不为空，两项条件都不通过。WHERE 不会因为把这一行筛掉，就回头补一行 NULL。

小陈没有任何订单，连接阶段本来就生成了补齐行，因此 o.id IS NULL 可以把他留下。小吴的 104 号订单是真实存在的，ID 非空，状态未知，也无法通过筛选。

还要区分 o.id IS NULL 与 o.status IS NULL。前者在本文主键非空的约束下，可以识别未匹配的补齐行；后者也会命中真实订单中未知的状态，不能代表“没有订单”。实际项目应选择有非空约束的右表字段判断是否匹配。

五、连接正确了，计数还可能多一行

报表通常还需要统计每个人的已支付订单数量。保留全部用户后，若直接使用 COUNT(*)，补齐行也会被计入：

SELECT u.id, u.name,
       COUNT(*) AS joined_rows,
       COUNT(o.id) AS paid_orders
FROM users u
LEFT JOIN orders o
  ON u.id = o.user_id AND o.status = 'paid'
GROUP BY u.id, u.name
ORDER BY u.id;

在这组数据中，四个人的 joined_rows 都是 1，paid_orders 分别是 1、0、0、0。COUNT(*) 统计结果行；COUNT(o.id) 不计 NULL，因此适合在这里统计真实匹配订单。

这个修正依赖 o.id 非空。如果改数一个允许 NULL 的字段，即使成功关联真实订单，也可能因为该字段为空而少算。若一个用户有多笔已支付订单，连接会为每笔订单输出一行；这属于一对多关系，不能随手加 DISTINCT 掩盖。

六、用三个边界检查报表含义

首先，把订单表清空。无额外筛选的 LEFT JOIN、筛选写在 ON 中的 LEFT JOIN，以及 OR 主键 IS NULL 的写法，都应返回四个用户；WHERE status=‘paid’ 和对应内连接都应返回零行。

其次，为小林再添加一笔已支付订单。ON 写法应返回五行，小林出现两次；WHERE 写法应返回两行，均属于小林。由此可以检查程序是否误把同一用户的多笔订单合并了。

最后，把真实订单的 status 改成 NULL。它可以按用户 ID 成功关联，但不会满足等于 paid 的判断。NULL 状态与右表整行未匹配是两件事，界面显示和后续统计都应区分。

实际排障时，先写出三句话：需要保留所有用户，还是只保留有已支付订单的人？一个用户是否可以产生多行？用于判断匹配和计数的右表字段是否保证非空？这三个答案通常比先调整 JOIN 关键字更能定位问题。

验证说明与参考

本文 SQL 使用独立的 SQLite 内存数据库验证，覆盖默认数据、空订单表、多笔已支付订单和 NULL 状态。示例没有连接生产数据库。其他数据库应结合自身字段约束和执行计划复核。

关于外连接补齐和 ON/WHERE 的语义，可查阅 PostgreSQL 官方文档《7.2. Table Expressions》；SQLite 的 SELECT 处理规则见 SQLite 官方文档《SELECT》。本文数据、查询对照和边界实验为独立构造。