SQL - JOIN 不完全指北

JOIN 是 SQL 中最核心的操作之一，但很多人只是机械记忆”INNER JOIN 取交集，LEFT JOIN 保留左表”。这篇文章用电影数据库的例子， 我整理了常用的JOIN，方便我们自查，真正理解它们的区别。

JOIN 类型对比速查表

JOIN 类型	返回规则	典型使用场景	示例需求
INNER JOIN	只返回两表都匹配的行	查询必须有关联关系的数据	查询有演员的电影 订单 + 用户信息（必须有用户）
LEFT JOIN	返回左表全部 + 右表匹配 右表无匹配显示 NULL	左表为主，右表可选 数据完整性检查	所有电影 + 演员（有的话） 查找没有订单的用户
RIGHT JOIN	返回右表全部 + 左表匹配 左表无匹配显示 NULL	右表为主，左表可选 （实际少用，建议用 LEFT JOIN）	所有演员 + 电影（有的话） 等价于调换顺序 + LEFT JOIN
FULL OUTER JOIN	返回两表全部行 无匹配的用 NULL 填充	数据完整性审计 找出不匹配的记录	找出：没演员的电影 + 没电影的演员 数据同步差异检查
CROSS JOIN	返回两表笛卡尔积 所有可能的组合	生成测试数据 组合穷举	所有产品 × 所有地区 所有尺寸 × 所有颜色
SELF JOIN	表和自己连接 查询同表内关系	层级关系 同事/合作关系	员工 - 经理关系 同一电影的演员对
LATERAL JOIN	子查询可引用外部表列 对左表每行执行子查询	Top-N per Group 关联计算	每部电影前 3 名演员 每个分类销量最高的产品

数据准备

假设我们有一个电影数据库，两张表：

movies 表：

id	title	year
1	Inception	2010
2	The Dark Knight	2008
3	Interstellar	2014
4	Tenet	2016

actors 表：

id	name	movie_id
1	Leonardo DiCaprio	1
2	Christian Bale	2
3	Matthew McConaughey	3
4	Anne Hathaway	3
5	Anne Hathaway	1
6	Tom Hardy	NULL

关键点：

• Tenet (movie_id=4) 在 movies 表里存在，但没有演员记录
• Tom Hardy (id=6) 的 movie_id 是 NULL，表示没有关联电影

INNER JOIN：只返回匹配的行

定义：返回两张表中 都有匹配 的记录。

需求：查询所有有演员出演的电影，显示电影名和演员名。

SELECT m.title, a.name
FROM movies m
INNER JOIN actors a ON m.id = a.movie_id
ORDER BY m.id, a.id;

输出结果：

title	name
Inception	Leonardo DiCaprio
Inception	Anne Hathaway
The Dark Knight	Christian Bale
Interstellar	Matthew McConaughey
Interstellar	Anne Hathaway

为什么是这个结果？

• Tenet 不在结果里：因为没有演员记录（actors 表中没有 movie_id=4）
• Tom Hardy 不在结果里：因为他的 movie_id 是 NULL，无法匹配任何电影

使用场景：查询”有演员的电影”或”有电影的演员” —— 两边都必须存在。

LEFT JOIN：保留左表所有行

定义：返回左表（FROM 后的表）的 所有记录，右表没匹配的用 NULL 填充。

需求：查询所有电影及其演员信息，即使电影没有演员也要显示。

SELECT m.title, a.name
FROM movies m
LEFT JOIN actors a ON m.id = a.movie_id
ORDER BY m.id, a.id;

输出结果：

title	name
Inception	Leonardo DiCaprio
Inception	Anne Hathaway
The Dark Knight	Christian Bale
Interstellar	Matthew McConaughey
Interstellar	Anne Hathaway
Tenet	NULL

为什么 Tenet 出现了？

• LEFT JOIN 保证 movies 表的所有行都会出现
• Tenet 在 actors 表没有匹配，所以 a.name 是 NULL

使用场景：查询”所有电影及其演员（如果有的话）” —— 即使电影没有演员也要显示。

LEFT JOIN + WHERE 过滤

需求：找出所有还没有演员出演的电影（数据质量检查）。

SELECT m.title, a.name
FROM movies m
LEFT JOIN actors a ON m.id = a.movie_id
WHERE a.id IS NULL;

输出结果：

title	name
Tenet	NULL

技巧：WHERE a.id IS NULL 过滤出右表没有匹配的行。

RIGHT JOIN：保留右表所有行

定义：返回右表（JOIN 后的表）的 所有记录，左表没匹配的用 NULL 填充。

需求：查询所有演员及其出演的电影，即使演员没有关联电影也要显示。

SELECT m.title, a.name
FROM movies m
RIGHT JOIN actors a ON m.id = a.movie_id
ORDER BY a.id;

输出结果（节选）：有电影的演员照常列出，无电影的演员如 Tom Hardy 对应 m.title 为 NULL。

title	name
…	…
NULL	Tom Hardy

为什么 Tom Hardy 出现了？

• RIGHT JOIN 保证 actors 表的所有行都会出现
• Tom Hardy 的 movie_id 是 NULL，无法匹配任何电影，所以 m.title 是 NULL

使用场景：查询”所有演员及其电影（如果有的话）” —— 即使演员没有关联电影也要显示。

实际建议：RIGHT JOIN 在实践中很少用，因为把表顺序调换 + LEFT JOIN 更直观：

-- 等价写法（更推荐）
SELECT m.title, a.name
FROM actors a
LEFT JOIN movies m ON m.id = a.movie_id
ORDER BY a.id;

FULL OUTER JOIN：保留两张表所有行

定义：返回 两张表的所有记录，没匹配的用 NULL 填充。

需求：数据完整性审计，找出所有电影和演员的关系，包括没有演员的电影和没有电影的演员。

SELECT m.title, a.name
FROM movies m
FULL OUTER JOIN actors a ON m.id = a.movie_id
ORDER BY m.id, a.id;

输出结果：匹配行正常；Tenet 无演员 → a.name 为 NULL；Tom Hardy 无电影 → m.title 为 NULL（仅示意两行）：

title	name
Tenet	NULL
NULL	Tom Hardy

为什么有两行 NULL？

• Tenet：左表有，右表无 → a.name 是 NULL
• Tom Hardy：右表有，左表无 → m.title 是 NULL

使用场景：数据完整性检查 —— 找出哪些电影没演员、哪些演员没电影。

只保留不匹配的行

需求：找出数据异常记录：哪些电影没有演员、哪些演员没有关联电影。

SELECT m.title, a.name
FROM movies m
FULL OUTER JOIN actors a ON m.id = a.movie_id
WHERE m.id IS NULL OR a.id IS NULL;

输出结果：

title	name
Tenet	NULL
NULL	Tom Hardy

技巧：WHERE m.id IS NULL OR a.id IS NULL 过滤出任一表没有匹配的行。

CROSS JOIN：笛卡尔积

定义：返回两张表的 所有可能组合（笛卡尔积 Cartesian Product）。

需求：生成所有可能的”电影-演员”配对（比如为选角推荐系统生成候选列表）。

SELECT m.title, a.name
FROM movies m
CROSS JOIN actors a
ORDER BY m.id, a.id;

结果：4 部电影 × 6 个演员 = 24 行（每部电影与每个演员各组合一次）。

使用场景：

• 生成测试数据：所有产品 × 所有地区的销售报表模板
• 组合生成：所有尺寸 × 所有颜色的商品库存初始化
• 警告：数据量大时会指数级增长，慎用！

SELF JOIN：表自己连接自己

定义：表和自己做 JOIN，用于查询 同一张表内的关系。

需求：找出曾在同一部电影中合作过的演员对。

SELECT 
  a1.name AS actor1,
  a2.name AS actor2,
  m.title AS movie
FROM actors a1
JOIN actors a2 ON a1.movie_id = a2.movie_id
JOIN movies m ON m.id = a1.movie_id
WHERE a1.id < a2.id  -- 避免重复（如 A-B 和 B-A）
ORDER BY m.title;

输出结果：

actor1	actor2	movie
Leonardo DiCaprio	Anne Hathaway	Inception
Matthew McConaughey	Anne Hathaway	Interstellar

要点：a1/a2 同一张表，a1.movie_id = a2.movie_id 找同片演员，WHERE a1.id < a2.id 避免重复对（A-B 与 B-A）。

LATERAL JOIN：让子查询能引用前面的表

定义：允许右侧的子查询 引用左侧表的列，类似编程语言中的”依赖注入”。

为什么需要 LATERAL JOIN？

普通子查询不能引用外部表：

-- ❌ 错误：子查询不能引用 m.id
SELECT m.title, 
  (SELECT a.name 
   FROM actors a 
   WHERE a.movie_id = m.id  -- 这里会报错
   LIMIT 1) as first_actor
FROM movies m;

传统做法是 JOIN + 窗口函数：先全量 JOIN，再按电影分区排序、取前 N。例如「每部电影前 2 名演员」：

-- 窗口函数：先 JOIN 再在子查询里筛 rn <= 2
SELECT title, name
FROM (
  SELECT m.title, a.name,
    ROW_NUMBER() OVER (PARTITION BY m.id ORDER BY a.id) AS rn
  FROM movies m
  LEFT JOIN actors a ON a.movie_id = m.id
) t
WHERE rn <= 2 OR rn IS NULL
ORDER BY title, rn;

LATERAL 不必先展开再筛，直接「每行取前 2」更直观，性能更加好。

基本用法

需求：查询每部电影的前 2 名演员（按演员 ID 排序）。

SELECT m.title, a.name
FROM movies m
LEFT JOIN LATERAL (
  SELECT name
  FROM actors
  WHERE movie_id = m.id  -- 引用外部表 m 的列
  ORDER BY id
  LIMIT 2
) a ON true
ORDER BY m.id;

输出结果：

title	name
Inception	Leonardo DiCaprio
Inception	Anne Hathaway
The Dark Knight	Christian Bale
Interstellar	Matthew McConaughey
Interstellar	Anne Hathaway
Tenet	NULL

解释：

• LATERAL 允许子查询访问 m.id，对左表每一行执行一次（类似循环）
• LIMIT 2 即「每部电影取前 2 名」——典型的 Top-N per Group，用窗口函数也能做，但 LATERAL 更简洁、常更省资源
• ON true 表示无条件连接，有些数据库，必须要有ON 子句否则会报错（过滤已在子查询内）

快速决策流程

需要两边都有数据？
  ├─ 是 → INNER JOIN
  └─ 否 → 继续

需要一边为主，另一边可选？
  ├─ 左表为主 → LEFT JOIN
  └─ 右表为主 → RIGHT JOIN (或调换顺序 + LEFT JOIN)

需要找出所有不匹配的记录？
  └─ 是 → FULL OUTER JOIN + WHERE 过滤

需要所有可能的组合？
  └─ 是 → CROSS JOIN (注意数据量)

查询同一张表内的关系？
  └─ 是 → SELF JOIN

需要每个分组取前 N 条？
  └─ 是 → LATERAL JOIN (PostgreSQL) 或窗口函数

常见误区

误区	正确理解
LEFT JOIN 一定比 INNER JOIN 慢	不一定，取决于数据分布和索引
RIGHT JOIN 和 LEFT JOIN 完全一样	语义不同，RIGHT JOIN 少用是为了统一代码风格
CROSS JOIN 没有 ON 条件就错了	CROSS JOIN 本来就不需要 ON，它是笛卡尔积
LATERAL 可以在任何数据库用	只有 PostgreSQL、Oracle 等支持，MySQL 不支持
多表 JOIN 一定要按顺序写	优化器会重排 JOIN 顺序，但清晰的顺序有助于理解

JOIN 性能优化：多层关系的困境

JOIN 本身不慢，但 多层 JOIN 会让查询变慢。

单层 JOIN（如查某部电影的演员）一次关联即可，通常很快。

多层 JOIN：性能下降

假设我们有第三张表 awards（奖项记录）：

id	actor_name	award_name	year
1	Leonardo DiCaprio	Oscar	2016
2	Christian Bale	Oscar	2011

需求：查询 Inception 这部电影的演员都获得过哪些奖项。

SELECT m.title, a.name, aw.award_name
FROM movies m
JOIN actors a ON m.id = a.movie_id
JOIN awards aw ON a.name = aw.actor_name  -- 第二层 JOIN
WHERE m.title = 'Inception';

性能问题：

• 第一次 JOIN：movies × actors
• 第二次 JOIN：结果 × awards
• 每多一层 JOIN，数据库需要扫描的行数可能指数增长

层数再增加（3 层、4 层…）同理，中间结果膨胀，性能会明显下降。多层关系（如「演员 → 奖项 → 机构 → 城市」）若成为常态，可考虑图数据库（Graph Database）：关系直接存储，遍历多跳更稳定。参考：Postgres 渐进迁移 Graph DB 的探索。

JOIN 条件：ON vs WHERE

很多人混淆 ON 和 WHERE 的区别。

规则

• ON：定义两张表的 关联条件（如何匹配行）
• WHERE：过滤 JOIN 后的结果

示例：INNER JOIN

需求：查询 2010 年之后上映的电影及其演员。

两种写法结果相同：

-- 写法 1：条件在 ON
SELECT m.title, a.name
FROM movies m
INNER JOIN actors a ON m.id = a.movie_id AND m.year > 2010;
 
-- 写法 2：条件在 WHERE
SELECT m.title, a.name
FROM movies m
INNER JOIN actors a ON m.id = a.movie_id
WHERE m.year > 2010;

两种写法结果相同（本例只剩 Interstellar 的两名演员）。INNER JOIN 时，过滤写在 ON 或 WHERE 对结果无影响。

示例：LEFT JOIN（关键区别）

需求：查询所有电影，并标注 Anne Hathaway 是否参演。

-- 写法 1：条件在 ON
SELECT m.title, a.name
FROM movies m
LEFT JOIN actors a ON m.id = a.movie_id AND a.name = 'Anne Hathaway';

输出结果：

title	name
Inception	Anne Hathaway
The Dark Knight	NULL
Interstellar	Anne Hathaway
Tenet	NULL

解释：LEFT JOIN 保留左表所有行，ON 条件只影响匹配逻辑：

• Inception 和 Interstellar 有 Anne Hathaway → 匹配成功
• The Dark Knight 和 Tenet 没有 Anne Hathaway → 右表显示 NULL

需求变化：只查 Anne Hathaway 参演的电影 → 把条件放进 WHERE a.name = 'Anne Hathaway'，JOIN 后再过滤，结果只剩两行（Inception、Interstellar）；左表未匹配到的行会被过滤掉。

结论

• INNER JOIN：ON 和 WHERE 效果相同
• LEFT/RIGHT/FULL JOIN：ON 影响匹配逻辑，WHERE 过滤最终结果

最佳实践：

• 关联条件写在 ON（如 m.id = a.movie_id）
• 数据过滤写在 WHERE（如 m.year > 2010）

可视化总结

graph TD
    A[两张表] --> B{JOIN 类型}
    B -->|INNER JOIN| C[只返回匹配行]
    B -->|LEFT JOIN| D[左表全部 + 右表匹配]
    B -->|RIGHT JOIN| E[右表全部 + 左表匹配]
    B -->|FULL OUTER JOIN| F[两表全部 + NULL 填充]
    B -->|CROSS JOIN| G[笛卡尔积所有组合]
    
    C --> H[使用场景]
    D --> H
    E --> H
    F --> H
    G --> H
    
    H --> I[INNER: 双边都必须存在]
    H --> J[LEFT: 左表为主右表可选]
    H --> K[FULL: 找出不匹配数据]
    H --> L[CROSS: 生成所有组合]

快速决策树

如何选择 JOIN 类型？

1. 需要两边都有数据？ → INNER JOIN
2. 左表为主，右表可选？ → LEFT JOIN
3. 右表为主，左表可选？ → RIGHT JOIN（或调换顺序 + LEFT JOIN）
4. 需要找出所有不匹配的数据？ → FULL OUTER JOIN + WHERE 过滤
5. 需要所有可能的组合？ → CROSS JOIN（小心数据量）

实践建议

1. 优先 INNER JOIN 和 LEFT JOIN：覆盖 95% 的场景
2. 避免 CROSS JOIN：除非数据量小且确实需要全组合
3. 多层 JOIN 慎重：超过 3 层考虑优化（索引、子查询、Graph Database）
4. 用 EXPLAIN 分析性能：看执行计划，找到慢查询的根源

EXPLAIN ANALYZE
SELECT m.title, a.name
FROM movies m
JOIN actors a ON m.id = a.movie_id;

5. 给 JOIN 字段加索引：movie_id 应该有索引，否则每次 JOIN 都是全表扫描