LEFT JOIN 与 RIGHT JOIN：外连接

INNER JOIN 的限制

回顾上一节的 INNER JOIN：

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

结果：

  name   | order_count
---------+-------------
 Alice   |           2
 Bob     |           2
 Charlie |           1

问题：Diana 没有出现！INNER JOIN 只返回两表都匹配的行。

如果想显示所有用户，包括没有订单的用户，需要 LEFT JOIN。

LEFT JOIN 基础

LEFT JOIN 返回左表的所有行，右表不匹配时用 NULL 填充。

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

结果：

  name   |  product   | amount
---------+------------+---------
 Alice   | Laptop     | 1200.00
 Alice   | Keyboard   |   80.00
 Bob     | Mouse      |   25.00
 Bob     | Headphones |   50.00
 Charlie | Monitor    |  300.00
 Diana   |            |

Diana 出现了，但 product 和 amount 是 NULL（她没有订单）。

LEFT JOIN 与聚合

统计每个用户的订单数（包括 0 个订单的用户）：

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

结果：

  name   | order_count
---------+-------------
 Alice   |           2
 Bob     |           2
 Charlie |           1
 Diana   |           0

关键点：

• COUNT(o.id) 只计数非 NULL 值
• COUNT(*) 会把 Diana 计为 1（包括 NULL 行）

对比：

SELECT u.name, COUNT(*) AS row_count, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

结果：

  name   | row_count | order_count
---------+-----------+-------------
 Alice   |         2 |           2
 Bob     |         2 |           2
 Charlie |         1 |           1
 Diana   |         1 |           0

找出未匹配的记录

找出没有订单的用户：

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

结果：

  name
--------
 Diana

工作原理：

1. LEFT JOIN 产生所有用户
2. Diana 的 o.id 是 NULL（没有匹配）
3. WHERE 过滤出 NULL 的行

这是查找"孤儿记录"的标准模式。

RIGHT JOIN

RIGHT JOIN 返回右表的所有行，左表不匹配时用 NULL 填充。

SELECT u.name, o.product
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

等价于：

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

推荐使用 LEFT JOIN（更直观），避免 RIGHT JOIN。

FULL OUTER JOIN

FULL OUTER JOIN 返回两表的所有行，不匹配时用 NULL 填充。

PostgreSQL 支持：

SELECT u.name, o.product
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

结果：

  name   |  product
---------+------------
 Alice   | Laptop
 Alice   | Keyboard
 Bob     | Mouse
 Bob     | Headphones
 Charlie | Monitor
 Diana   |            -- Diana 没有订单
         | Orphan     -- 假设有孤儿订单（user_id 不存在）

MySQL 不支持 FULL OUTER JOIN，需要用 UNION 模拟：

SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.product
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

实际使用中，FULL OUTER JOIN 较少见。

多个 LEFT JOIN

可以连续使用多个 LEFT JOIN：

SELECT u.name, o.product, c.name AS category
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN categories c ON o.category_id = c.id;

结果：

  name   |  product   |  category
---------+------------+-------------
 Alice   | Laptop     | Electronics
 Alice   | Keyboard   | Electronics
 Bob     | Mouse      | Electronics
 Bob     | Headphones | Electronics
 Charlie | Monitor    | Electronics
 Diana   |            |

注意顺序：先 LEFT JOIN orders，再 LEFT JOIN categories。

LEFT JOIN 与 WHERE 的陷阱

WHERE 在 JOIN 之后执行，会过滤掉 NULL 行：

-- 错误：Diana 被过滤掉了
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

结果：

  name   | product
---------+---------
 Alice   | Laptop
 Charlie | Monitor

Diana 消失了！因为 WHERE 过滤掉了 o.amount IS NULL 的行。

正确做法 1：把条件放在 ON 中

SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;

结果：

  name   | product
---------+---------
 Alice   | Laptop
 Bob     |        
 Charlie | Monitor
 Diana   |

所有用户都出现，但只显示符合条件的订单。

正确做法 2：用 COALESCE 处理 NULL

SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100 OR o.amount IS NULL;

实际应用场景

场景 1：找出所有用户及其最近订单

SELECT u.name, MAX(o.order_date) AS last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

结果：

  name   | last_order
---------+------------
 Alice   | 2026-01-17
 Bob     | 2026-01-19
 Charlie | 2026-01-18
 Diana   |

场景 2：数据完整性检查

-- 找出有订单但用户不存在的记录（孤儿订单）
SELECT o.id, o.product
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

场景 3：可选关系

-- 员工及其可选的经理
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

结果：

 employee | manager
----------+---------
 Alice    |        -- CEO 没有经理
 Bob      | Alice
 Charlie  | Alice
 Diana    | Bob

LEFT JOIN vs INNER JOIN 对比

特性	INNER JOIN	LEFT JOIN
左表记录	只返回匹配的	返回所有
右表记录	只返回匹配的	匹配的或 NULL
结果行数	≤ 左表行数	= 左表行数（或更多）
用途	必须关联	可选关联

示例对比：

-- INNER：3 行（只有有订单的用户）
SELECT u.name FROM users u INNER JOIN orders o ON u.id = o.user_id GROUP BY u.name;

-- LEFT：4 行（所有用户）
SELECT u.name FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name;

性能考虑

• LEFT JOIN 通常比 INNER JOIN 慢（需要处理 NULL）
• 索引外键列（user_id）可以显著提升性能
• 避免不必要的 LEFT JOIN（确定关系存在时用 INNER JOIN）

练习

1. 基本 LEFT JOIN：查询所有用户及其订单数量（包括 0 个订单的用户）。

2. 找出孤儿：找出没有订单的用户。

3. 聚合统计：计算每个用户的总消费金额，包括没有消费的用户（显示为 0）。

4. 多表 LEFT JOIN：连接 users、orders、categories，显示所有用户，即使没有订单或类别。

5. WHERE vs ON：对比以下两个查询的区别：

   • LEFT JOIN ... WHERE amount > 100
   • LEFT JOIN ... ON ... AND amount > 100

6. 可选经理：查询所有员工及其经理，包括没有经理的员工（CEO）。

7. 数据完整性：找出 orders 表中 user_id 不存在于 users 表的记录。

8. 实际场景：查询每个用户的最新订单日期和产品，没有订单的显示 NULL。

完成练习后，打开本章答案页核对。

下一步

学会了 LEFT JOIN 和外连接。下一节学习子查询：如何在查询中嵌套查询，实现更复杂的逻辑。

回到第三章概览 · 返回SQL 学习路线