用一个实际业务场景演示ON和WHERE的正确使用方式

为了让你直观理解 ON 和 WHERE 的区别，我们构建一个经典的电商业务场景：“统计所有用户的订单情况，但只关注‘已支付’的订单，且只看‘北京’地区的用户”。

1. 场景准备
   假设我们有两张表：

• 用户表 (users)：包含 id, name, city
• 订单表 (orders)：包含 id, user_id, amount, status ('paid', 'unpaid')

数据示例：
| users.id | users.name | users.city orders.user_id | orders.status | orders.amount |
| :--- | :--- | :--- | :--- | :--- | :--- |
| 1 | 张三 | 北京 | 1 | paid | 100 |
| 2 | 李四 | 北京 | 2 | unpaid | 200 |
| 3 | 王五 | 上海 | NULL | NULL | NULL |

业务目标：列出所有北京用户，并显示他们已支付的订单金额。如果用户没有已支付订单，金额显示为 NULL（即保留用户，但不显示未支付或无订单的数据）。

---

2. 错误写法 vs 正确写法对比

❌ 错误写法：将右表过滤条件放在 WHERE 中
很多初学者会这样写，认为“反正都是过滤条件”：

SELECT 
    u.name, 
    o.amount 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE 
    u.city = '北京'          -- 左表过滤：没问题
    AND o.status = 'paid';   -- 【陷阱】右表过滤放在 WHERE

执行逻辑与结果：

1. JOIN：先进行左连接，生成临时表。此时李四（ID=2）会匹配到一条 status='unpaid' 的记录；王五（ID=3）匹配到 NULL。
2. WHERE：对临时表进行全局过滤。
   • 张三：city='北京' 且 status='paid' -> 保留。
   • 李四：city='北京' 但 status='unpaid' -> 被剔除（因为 o.status 不为 'paid'）。
   • 王五：city='上海' -> 被剔除。
   • 关键问题：如果有一个北京用户没有任何订单（o.status 为 NULL），他也会因为 NULL != 'paid' 而被 WHERE 子句剔除。
3. 后果：这实际上把 LEFT JOIN 退化成了 INNER JOIN 的效果，丢失了“有用户但无已支付订单”的数据。如果你原本想保留李四（显示金额为 NULL），这里李四直接消失了。

---

✅ 正确写法：右表过滤放 ON，左表过滤放 WHERE

SELECT 
    u.name, 
    o.amount 
FROM users u
LEFT JOIN orders o 
    ON u.id = o.user_id 
    AND o.status = 'paid'   -- 【核心】右表过滤放在 ON 中
WHERE 
    u.city = '北京';         -- 【核心】左表过滤放在 WHERE 中

执行逻辑与结果：

1. ON (连接阶段)：

   • 数据库尝试将 users 和 orders 匹配。
   • 匹配规则是：id 相等 且 订单状态必须是 'paid'。
   • 张三 (ID=1)：匹配到 ID=1 的已支付订单 -> 成功连接。
   • 李四 (ID=2)：虽然有 ID=2 的订单，但状态是 'unpaid'，不满足 AND o.status = 'paid' -> 连接失败，右表字段补 NULL。
   • 王五 (ID=3)：无订单 -> 连接失败，右表字段补 NULL。
   • 注意：此时左表（users）的所有行都还在临时表中。

2. WHERE (过滤阶段)：

   • 对连接后的临时表进行筛选。
   • 张三：city='北京' -> 保留。
   • 李四：city='北京' -> 保留（此时 o.amount 为 NULL）。
   • 王五：city='上海' -> 剔除。

3. 最终结果：

   name	amount
   张三	100
   李四	NULL

   解读：我们成功保留了李四，虽然他没有“已支付”订单，但他作为“北京用户”依然出现在列表中，符合“统计所有北京用户”的业务初衷。

---

3. 核心原则总结（口诀）

为了方便记忆，请遵循以下原则：

条件类型	放置位置	原因
关联键 (如 a.id = b.id)	ON	定义表之间如何“握手”。
右表过滤 (被连表)	ON	在“握手”时就排除不想要的右表数据，确保左表数据不因右表不匹配而丢失。
左表过滤 (主表)	WHERE	左表数据在 JOIN 后已经完整保留，最后再筛选哪些左表行需要展示。
内连接 (INNER JOIN)	ON 或 WHERE	效果一样，但建议关联放 ON，过滤放 WHERE，语义更清晰。

4. 为什么这样性能更好？

• 提前剪枝：将 o.status = 'paid' 放在 ON 中，数据库在连接过程中就可以忽略那些“未支付”的订单行，减少参与连接的数据量，从而提升性能。
• 避免回退：如果放在 WHERE 中，数据库可能需要先连接大量无效数据（如未支付订单），生成巨大的中间临时表，然后再通过 WHERE 丢弃它们，浪费内存和 CPU。

一句话总结：
想保留主表所有行（LEFT JOIN），过滤从表（右表）的条件必须写在 ON 里；过滤主表（左表）的条件写在 WHERE 里。

参考文章：
https://www.cnblogs.com/east7/p/16703734.html