菜单
Jeeinn's Blog
2020-03-12
深入理解MySQL的LEFT JOIN：ON与WHERE条件的正确使用

LEFT JOIN是SQL中常用的连接操作，但对其条件放置（ON vs WHERE）的误解易导致查询错误。本文通过核心要点和可复现实例，解析两者差异。

❗

通常用于一对一的关联关系，当一对多时需要额外关注条件以免不符合预期

一、LEFT JOIN 的核心逻辑

LEFT JOIN 以左表为基准，返回所有行。右表无匹配时，字段补 NULL。

  1. 匹配阶段（ON 条件）\ 根据 ON 后的条件，将左表的每一行与右表进行匹配。
    • 若右表有匹配行，则填充对应字段。
    • 若无匹配，则右表字段全部填充为 NULL。

      ✅ 关键：左表的所有行都会保留，无论是否匹配成功。

  2. 过滤阶段（WHERE 条件）\ 在 JOIN 完成后，对整个上一步完整结果集应用 WHERE 条件，过滤掉不符合条件的行。

二、ON 与 WHERE 的关键区别

  1. 对左表条件的处理：
    • 条件放ON中（如ON left_table.id = right_table.id AND left_table.col = 'value'）：左表所有行返回，仅影响右表匹配。
    • 条件放WHERE中（如WHERE left_table.col = 'value'）：可能减少左表返回行数。
  2. 对右表条件的处理：
    • 条件放ON中：右表匹配失败时补NULL，左表行数不变。
    • 条件放WHERE中：可能过滤掉右表为NULL的行，需谨慎避免意外结果。

三、实战场景举例

场景：查询用户及其订单（订单金额大于150）

表结构：

  • users（左表）：id, name
  • orders（右表）：id, user_id, amount

数据准备：

 -- 用户表（左表）
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(10)
);
INSERT INTO users VALUES 
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- 订单表（右表）
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(5,2)
);
INSERT INTO orders VALUES 
(101, 1, 100.00),
(102, 1, 200.00),  -- Alice 有两笔订单
(103, 2, 50.00);

users: 
id | name
1  | Alice
2  | Bob
3  | Charlie

orders:
id   | user_id | amount
101  | 1       | 100
102  | 1       | 200
103  | 2       | 50
  • 仅连接(Alice 有两笔订单)
SELECT users.name, orders.amount 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

-- 结果
name    | amount
Alice   | 200
Alice   | 100
Bob     | 50
Charlie | Null
  • 过滤写法（直接过滤右表）：
SELECT users.name, orders.amount 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id 
WHERE orders.amount > 150; -- 会过滤掉无订单的用户

-- 结果
name  | amount
Alice | 200    -- 匹配订单
  • 保留写法（保留无订单用户）：
SELECT users.name, orders.amount 
FROM users 
LEFT JOIN orders ON (users.id = orders.user_id AND orders.amount > 150);

# 结果
name    | amount
Alice   | 200    -- 匹配订单
Bob     | NULL   -- 金额≤150的订单被ON条件排除，但左表行保留
Charlie | NULL   -- 无订单，右表补NULL

四、总结

  • ON条件用于控制表间连接行为，保留左表完整性。
  • WHERE条件用于最终结果过滤，可能改变LEFT JOIN的左表基准特性。
  • 实际开发中，若需保留左表全部行，右表过滤条件应放在ON中；若需过滤整体结果，再用WHERE。

验证方式：使用任意SQL数据库（如MySQL、PostgreSQL）创建上述表结构并插入示例数据，执行查询即可复现结果。

Powered by Tunalog 🐟