JOIN 多表联结

这一课你将能做到

1. 用 INNER JOIN 把 2~4 张表按外键关系拼成一张宽结果
2. 用 LEFT JOIN + IS NULL 找出「没有匹配」的行（没有课时的课程、没学过习的学生）
3. 说清 ON 与 WHERE 的区别，避开 LEFT JOIN 被 WHERE「吃掉」的经典坑

前置：第一课建库脚本已跑；本课查询会用到第 6 课会改动数据，若你在前面课删过课时，先回第一课重跑脚本复原数据，保证输出一致。

为什么要学

第一课就说了：关系型数据库的纪律是「同一事实只存一次」。代价是数据拆在多张表——study_record 里只有 student_id 和 lesson_id 两个数字，单表查询根本看不出「谁、学了哪节课、考了多少分」。JOIN 就是把这些拆开的表按外键拼宽的语言结构，是关系型数据库相对 Excel 的核心能力，也是面试必考。

先建立图像

JOIN 按「拼接条件」把两张表的行配对。配得上就拼到一起，配不上 Inner 直接丢、Left 用 NULL 补位：

INNER JOIN course ⋈ lesson          LEFT JOIN course ⋈ lesson
（只保留两边都配上的）                （左表全保留，右表缺位补 NULL）

course          lesson               course          lesson
1 ●─────────● 1                    1 ●─────────● 1
2 ●─────────● 2                    2 ●─────────● 2
3 ●─────────● 3                    3 ●─────────● 3
7 ○（没课时，被丢掉）                7 ○──→ NULL（保留，右侧补空）

本练习库的关系图（记住它，后面 6 课都用）：

student 1 ──< study_record >── 1 lesson >── 1 course
                                      │
student 1 ──< pay_order >──────────────┘（订单直接挂课程）

跟着做

步骤 1：感受「单表查不出人话」

SELECT student_id, lesson_id, score FROM study_record LIMIT 5;
-- 1 6 92    1 7 88    1 1 95 …… 谁是 1？6 是哪节课？不知道

数字 id 只对机器有意义，人需要名字和标题——这就是 JOIN 存在的理由。

步骤 2：两表 INNER JOIN

SELECT r.id, l.title, r.score
FROM study_record r
JOIN lesson l ON r.lesson_id = l.id;
-- 17 行：每条学习记录配上了课时标题

读法：从 study_record（别名 r）出发，拿 r.lesson_id 去匹配 lesson.id，配上的行拼在一起。JOIN 是 INNER JOIN 的简写。

步骤 3：三表、四表联结（真实接口长这样）

SELECT s.name, c.title AS course_title, l.title AS lesson_title, r.score
FROM study_record r
JOIN student s ON r.student_id = s.id
JOIN lesson l  ON r.lesson_id  = l.id
JOIN course c  ON l.course_id  = c.id
WHERE r.completed = 1
ORDER BY r.score DESC
LIMIT 5;
-- 阿珍 98（SQL 是什么）/ 老陈 95（环境搭建）/ 阿珍 95（SELECT 基本查询）
-- 老周 92（EXPLAIN 详解）/ 老陈 92（变量与数据类型）

一条语句串了 4 张表，这就是「学习记录页」接口背后的查询原型。

步骤 4：LEFT JOIN —— 左表全保留

先看「哪些课程连一节课都没有」：

SELECT c.title
FROM course c
LEFT JOIN lesson l ON l.course_id = c.id
WHERE l.id IS NULL;
-- 1 行：Git 团队协作基础（造数时它就没有课时）

原理：LEFT JOIN 保证左表（course）每一行都出现；右表配不上的行右侧全是 NULL。于是「右表主键 IS NULL」就等价于「左表在右表里没有匹配」——这个套路叫反连接（anti-join），面试高频。

再来一个同款：

SELECT s.name
FROM student s
LEFT JOIN study_record r ON r.student_id = s.id
WHERE r.id IS NULL;
-- 1 行：小吴（造数时他没有任何学习记录）

步骤 5：LEFT JOIN 数个数 —— COUNT 陷阱现场

SELECT c.title, COUNT(l.id) AS lesson_cnt
FROM course c
LEFT JOIN lesson l ON l.course_id = c.id
GROUP BY c.id, c.title;
-- Java 基础入门 3 / SQL 与关系型数据库 4 / …… / Git 团队协作基础 0

这里必须 COUNT(l.id) 而不是 COUNT(*)：Git 那行右侧是 NULL，COUNT(*) 会把它数成 1，COUNT(l.id) 数到 NULL 才返回 0。这是 LEFT JOIN + COUNT 最著名的坑。

步骤 6：ON 与 WHERE 的区别（LEFT JOIN 专属考点）

右表的条件放在 ON 里，左表行保留、右表缺位补 NULL：

SELECT c.title, l.title AS lesson_title
FROM course c
LEFT JOIN lesson l ON l.course_id = c.id AND l.status = 'published'
ORDER BY c.id;
-- course 全部 7 行都在；git-essentials 的 lesson_title 为 NULL

同样的条件若放进 WHERE，LEFT JOIN 就退化成 INNER JOIN（NULL 行被过滤掉了），「保留全部左表」的意图直接作废。INNER JOIN 时 ON 和 WHERE 等价；OUTER JOIN 时语义完全不同。

讲清楚要点

1. JOIN 的本质是按条件拼宽行：ON 是配对条件，通常写成「左表外键 = 右表主键」。漏写 ON 会得到笛卡尔积（7×14=98 行的乱配对），MySQL 8 已经会直接报错拦住。
2. INNER vs LEFT 的选择标准：要「两边都存在」的记录用 INNER；要「左表全量、右表可能缺」用 LEFT。RIGHT JOIN 几乎不用——把两张表换个位置写成 LEFT 就行，语义更顺。
3. 反连接套路：LEFT JOIN … WHERE 右表.主键 IS NULL = 查「左表里没有匹配的行」。比 NOT IN 安全（不怕 NULL），面试让写「没有订单的用户」就答这个。
4. ON 与 WHERE 的分工：INNER 随意；LEFT JOIN 时右表条件进 ON、左表条件进 WHERE。记不准就问自己：这行左表数据「没有右表匹配时，还要不要出现」？要，就放 ON。
5. 连接列要有索引：InnoDB 的外键会自动建索引（本练习库 lesson.course_id、study_record.student_id 都有）；自己 JOIN 的普通列要手动建，否则大表 JOIN 是全表互扫。第 8 课 EXPLAIN 验证。
6. 优化器会重排 JOIN 顺序：你只管把语义写对（小表驱动大表这类事交给 MySQL 8），可读性用表别名和 CTE 保证。

你可能会懵的地方

• JOIN 出来的行数比预期多好几倍？ 一对多关系放大了行数：一个课程 4 节课，JOIN 后课程这行出现 4 次。这是正常现象；要「每课程一行」得配合 GROUP BY 聚合（下一课）。行数暴涨到离谱则多半是 ON 条件写错。

• FULL OUTER JOIN 为什么 MySQL 没有？ MySQL 8 不支持，需要时用 LEFT JOIN … UNION … RIGHT JOIN 模拟。实践中极少用到——大多数「两边都要」的需求其实是两笔独立统计。

• 自连接是什么？ 一张表和自己 JOIN，用于表内有层级关系（如员工表的 manager_id 指向同表）。本练习库没有树结构，先记住概念；本站的 cf_roadmap 节点树、组织架构树都是这个用法。

• JOIN 和子查询哪个好？ 大多数场景优化器会改写成等价计划，性能差不多。表达「每行配一条信息」用 JOIN 更直观；表达「存在/不存在」用 EXISTS / NOT EXISTS 更清晰。别教条。

• (+,) 这种老 Oracle 写法、逗号 JOIN（FROM a, b WHERE…）还能用吗？ 都过时了。逗号 JOIN 容易漏条件，显式 JOIN … ON 是唯一推荐写法。

错法对照

错法	问题	正解
忘写 ON	笛卡尔积 / 直接报语法错	每个 JOIN 必配 ON
LEFT JOIN 后把右表条件写进 WHERE	退化成 INNER，缺匹配的左表行消失	右表条件移进 ON
LEFT JOIN 后用 COUNT(*)	没匹配的组被数成 1	COUNT(右表主键)
JOIN 列没索引	大表互扫，慢到超时	外键自动建，普通列手动建
两表都有 id 列却裸写 id	Column 'id' is ambiguous	一律 表别名.列名
用 RIGHT JOIN 追求「以右表为准」	阅读顺序反人类	交换表序用 LEFT JOIN

卡点急救

1. Unknown column 'xxx' in 'on clause'：别名拼错或列名前忘了写表别名。四表联结时坚持 r. s. l. c. 前缀。
2. 结果行数不对：先数「一对多放大了多少倍」，再查 ON 条件是否漏了一半；含 IS NULL 的反连接要确认判的是右表主键。
3. 想要的行不见了：九成是 LEFT JOIN 被 WHERE 的右表条件吃了。把右表条件挪进 ON 再看。
4. 查询超慢：EXPLAIN 看连接列是否全表扫描（type=ALL），给缺索引的列补索引；细节在第 8 课。

面试常问

• INNER JOIN、LEFT JOIN、RIGHT JOIN 的区别？各举一个场景。 INNER 只要匹配行（订单明细关联商品）；LEFT 保留左表全量（课程列表要展示没课时的课）；RIGHT 以右表为准，实践中换成 LEFT 写。能否答出「LEFT JOIN + IS NULL 反连接」是加分线。

• 怎么查「没有下过单的用户」？ LEFT JOIN pay_order … WHERE pay_order.id IS NULL，或 NOT EXISTS (SELECT 1 FROM pay_order WHERE …)。顺带说明为什么不用 NOT IN（NULL 陷阱）。

• ON 和 WHERE 的区别？ INNER 等价；OUTER 不同：ON 在匹配阶段生效（缺匹配补 NULL），WHERE 在结果上过滤（会把补 NULL 的行筛掉）。LEFT JOIN 想保留左表全量，右表条件必须写 ON。

• JOIN 慢怎么排查？ 看连接列有没有索引、驱动表和被驱动表的行数量级、EXPLAIN 的 type/rows；数据倾斜或大表 JOIN 考虑先过滤再联。

自测三问

1. 写查询：每门课程标题 + 已发布课时数（没有课时的课程显示 0）。
2. 为什么 LEFT JOIN 里 COUNT(*) 和 COUNT(l.id) 结果不同？
3. 「查所有从未购课的学生」用哪两种写法？NOT IN 有什么坑？

小结一句话

JOIN 按外键把拆开的表拼宽：INNER 要交集，LEFT 保左表全量，LEFT JOIN + 右表主键 IS NULL 找「没有匹配」；LEFT JOIN 的右表条件放 ON 不放 WHERE。

延伸

• MySQL JOIN 语法
• 下一课：聚合与 GROUP BY——把 JOIN 出来的明细收拢成统计报表。