03 多表连接查询
📖 本节导读
- 明白为什么数据要拆成多张表存，而查询时又要把它们"拼"回来。
- 理解笛卡尔积——所有连接的底层原理，也是新手最容易翻车的地方。
- 掌握内连接 INNER JOIN ... ON，完成"学生姓名 + 课程名 + 成绩"的三表连接。
- 掌握左外连接 LEFT JOIN，看懂"没有的数据显示 NULL"这一关键行为，并了解右连接、表别名和自连接。
一、为什么要拆成多张表
假设我们不拆表，把所有信息塞进一张"大宽表"：
+-----------+--------+------------+--------+-----------+-----------+-------+
| 学生 | 班级 | 生日 | 课程 | 老师 | 性别 | 成绩 |
+-----------+--------+------------+--------+-----------+-----------+-------+
| 张三 | 一班 | 2005-03-15 | 数学 | 王老师 | 男 | 90.0 |
| 张三 | 一班 | 2005-03-15 | 英语 | 李老师 | 男 | 85.0 |
| 张三 | 一班 | 2005-03-15 | 计算机 | 张老师 | 男 | 88.0 |
| 李四 | 一班 | 2005-07-01 | 数学 | 王老师 | 男 | 76.0 |
| ... | ... | ... | ... | ... | ... | ... |
+-----------+--------+------------+--------+-----------+-----------+-------+
问题一眼可见：张三的班级、生日被抄了 3 遍，"数学-王老师"也被抄了 4 遍。这叫数据冗余。冗余不只是浪费空间——假如张三转到二班，就得改 3 行，漏改一行数据就"自相矛盾"了。
所以数据库设计的思路是一类事物一张表：class 存班级、student 存学生、course 存课程、score 只存"谁的哪门课考了多少分"（用 student_id、course_id 两个编号去指向别的表）。每个事实只存一份，改一处即可。
代价是：查询"张三的数学成绩"时，信息分散在三张表里，需要把表**连接（JOIN）**起来。连接就是本节的主角。
二、笛卡尔积：连接的"原材料"
如果把两张表放进 FROM 却不写任何连接条件：
SELECT student.name, class.name FROM student, class;
+-----------+--------+
| name | name |
+-----------+--------+
| 张三 | 一班 |
| 张三 | 二班 |
| 李四 | 一班 |
| 李四 | 二班 |
| 王小红 | 一班 |
| 王小红 | 二班 |
| 赵敏 | 一班 |
| 赵敏 | 二班 |
| 陈刚 | 一班 |
| 陈刚 | 二班 |
+-----------+--------+
10 rows in set (0.00 sec)
5 个学生 × 2 个班级 = 10 行：左表的每一行都和右表的每一行配一次对，这就是笛卡尔积。类比：5 个男生和 2 个女生跳交谊舞，不加规则的话会产生 5 × 2 = 10 种配对，其中大部分毫无意义（张三明明是一班的，却也和二班配了对）。
连接查询的本质就是：先做笛卡尔积，再用连接条件把"配错对"的行筛掉。如果两张表各有 1 万行，笛卡尔积是 1 亿行——所以连接条件几乎永远不能省。
三、内连接 INNER JOIN ... ON
两表连接：学生 + 班级
SELECT student.name AS 姓名, class.name AS 班级
FROM student
INNER JOIN class ON student.class_id = class.id;
+-----------+--------+
| 姓名 | 班级 |
+-----------+--------+
| 张三 | 一班 |
| 李四 | 一班 |
| 王小红 | 二班 |
| 赵敏 | 二班 |
| 陈刚 | 一班 |
+-----------+--------+
5 rows in set (0.00 sec)
INNER JOIN 表名：要连接哪张表（INNER 可省略，直接写 JOIN）。ON 条件：怎样才算"配对成功"——这里是"学生的 class_id 等于班级的 id"。- 内连接只保留两边都能配上对的行。
表别名：给表起个小名
表名写全太啰嗦，可以在 FROM / JOIN 里给表起别名（AS 可省略），之后全句都用小名：
SELECT s.name AS 姓名, c.name AS 班级
FROM student AS s
INNER JOIN class AS c ON s.class_id = c.id;
结果与上面完全相同。注意：一旦起了别名，原表名就不能再用了，student.name 要写成 s.name。
三表连接：学生姓名 + 课程名 + 成绩
score 表里只有 student_id 和 course_id 两个编号，要想显示名字，需要连三张表——多连一张表，就多写一组 JOIN ... ON：
SELECT s.name AS 学生, c.name AS 课程, sc.score AS 成绩
FROM score AS sc
INNER JOIN student AS s ON sc.student_id = s.id
INNER JOIN course AS c ON sc.course_id = c.id;
+-----------+-----------+--------+
| 学生 | 课程 | 成绩 |
+-----------+-----------+--------+
| 张三 | 数学 | 90.0 |
| 张三 | 英语 | 85.0 |
| 张三 | 计算机 | 88.0 |
| 李四 | 数学 | 76.0 |
| 李四 | 英语 | 92.0 |
| 王小红 | 数学 | 95.0 |
| 王小红 | 英语 | 60.0 |
| 王小红 | 计算机 | 70.0 |
| 赵敏 | 数学 | 58.0 |
| 赵敏 | 计算机 | 81.0 |
| 陈刚 | 英语 | 73.0 |
| 陈刚 | 计算机 | NULL |
+-----------+-----------+--------+
12 rows in set (0.00 sec)
score 表 12 行，每行都能在 student 和 course 里配上对，所以结果也是 12 行。注意最后一行：陈刚的计算机记录存在，只是成绩值是 NULL，所以内连接照样能查出来。
连接查询与 WHERE 组合
连接之后照常可以接 WHERE、ORDER BY 等。比如查数学成绩并按分数降序：
SELECT s.name AS 学生, sc.score AS 数学成绩
FROM score AS sc
INNER JOIN student AS s ON sc.student_id = s.id
WHERE sc.course_id = 1
ORDER BY sc.score DESC;
+-----------+--------------+
| 学生 | 数学成绩 |
+-----------+--------------+
| 王小红 | 95.0 |
| 张三 | 90.0 |
| 李四 | 76.0 |
| 赵敏 | 58.0 |
+-----------+--------------+
4 rows in set (0.00 sec)
有没有发现：陈刚不见了。因为 score 表里根本没有"陈刚 + 数学"这条记录，内连接配不上对，他就被无声地丢掉了。想让他也出现，就要用左外连接。
四、左外连接 LEFT JOIN
LEFT JOIN 的规则：左表（写在 JOIN 左边的表）的每一行都必须出现在结果里；右表配得上就填数据，配不上就整行填 NULL。
查"每个学生的数学成绩"，没有记录的也要显示：
SELECT s.name AS 学生, sc.score AS 数学成绩
FROM student AS s
LEFT JOIN score AS sc
ON sc.student_id = s.id AND sc.course_id = 1;
+-----------+--------------+
| 学生 | 数学成绩 |
+-----------+--------------+
| 张三 | 90.0 |
| 李四 | 76.0 |
| 王小红 | 95.0 |
| 赵敏 | 58.0 |
| 陈刚 | NULL |
+-----------+--------------+
5 rows in set (0.00 sec)
这回 5 个学生一个不少，陈刚的数学成绩显示为 NULL——这个 NULL 是 LEFT JOIN "补"出来的，表示"右表里没找到能配对的行"。
用文字图对比一下（以"学生 ↔ 数学成绩记录"为例）：
student（左表） score 中 course_id=1 的行（右表）
张三 ──────配对成功────── (1, 1, 90.0)
李四 ──────配对成功────── (4, 2, 76.0)
王小红 ──────配对成功────── (6, 3, 95.0)
赵敏 ──────配对成功────── (9, 4, 58.0)
陈刚 ──✗ 没有可配对的行
INNER JOIN：只输出 4 条"配对成功"的行，陈刚被丢弃
LEFT JOIN ：5 行全保留，陈刚的右表部分用 NULL 填充
再用表格总结两者差异：
| 情况 | INNER JOIN | LEFT JOIN |
|---|---|---|
| 两边都能配上 | 保留 | 保留 |
| 左表有、右表没有 | 丢弃 | 保留，右表列补 NULL |
| 右表有、左表没有 | 丢弃 | 丢弃 |
⚠️ 细节：上面把
sc.course_id = 1写在 ON 里而不是 WHERE 里。如果写成WHERE sc.course_id = 1，陈刚那行补出来的 course_id 是 NULL，NULL = 1不成立，整行又会被 WHERE 过滤掉——LEFT JOIN 就白做了。记住：对右表的过滤条件放 ON，对左表的过滤条件放 WHERE。
另外分清两种"没有"：
- 陈刚 + 数学：score 表里根本没这行，靠 LEFT JOIN 补出 NULL；
- 陈刚 + 计算机：score 表里有这行，只是 score 列本身存的就是 NULL。 两者在结果里长得一样，含义却不同。
五、右连接 RIGHT JOIN（一句话带过）
RIGHT JOIN 与 LEFT JOIN 完全对称：保全右表的所有行。A LEFT JOIN B 等价于 B RIGHT JOIN A，所以实践中几乎都统一写 LEFT JOIN（把要保全的表放左边），不必专门记右连接。
六、自连接：自己连自己
一张表也可以和"自己的副本"连接，只要起两个不同的别名，就当成两张表用。比如"找出和张三同班的其他学生"：
SELECT b.name AS 同班同学
FROM student AS a
INNER JOIN student AS b ON a.class_id = b.class_id
WHERE a.name = '张三' AND b.name <> '张三';
+--------------+
| 同班同学 |
+--------------+
| 李四 |
| 陈刚 |
+--------------+
2 rows in set (0.00 sec)
a 扮演"张三那一行"，b 扮演"全班同学"，靠 class_id 相等配对。自连接在员工-上司、分类-父分类等场景很常用，这里先混个脸熟。
⚠️ 新手常见坑
- 忘写 ON 条件：直接得到笛卡尔积，行数爆炸且结果毫无意义。看到结果行数异常多，先检查连接条件。
- 该用 LEFT JOIN 时用了 INNER JOIN："查每个学生的 XX"这种要求全员出现的需求，用内连接会悄悄丢掉没有记录的人（比如陈刚）。
- LEFT JOIN 后把右表条件写进 WHERE：会把补出来的 NULL 行过滤掉，效果退化成内连接。右表的筛选条件应写在 ON 里。
- 多表有同名列不加前缀：student 和 course 都有 name 列，直接写
name会报Column 'name' in field list is ambiguous，必须写s.name、c.name。 - 起了别名又用原表名：FROM student AS s 之后再写 student.name 会报"未知的列"，别名一旦声明就要贯彻到底。
📝 小结
- 拆表是为了消除冗余、便于修改；查询时用 JOIN 把相关表按"编号相等"拼回来。
- 笛卡尔积 = 左表每行 × 右表每行；连接 = 笛卡尔积 + ON 条件筛选。
- INNER JOIN 只保留两边都配上对的行；LEFT JOIN 保全左表所有行，右表配不上就补 NULL；RIGHT JOIN 是反方向的 LEFT JOIN。
- 表别名让 SQL 更简洁，也是自连接（同表两个身份）的前提。
- 口诀：要求"每个 X 都要出现"→ X 放左边用 LEFT JOIN；只要"有记录的"→ INNER JOIN。
✍️ 练习题
- 查询每个班级的名称和班级人数（连接 class 和 student，分组统计）。
参考答案
SELECT c.name AS 班级, COUNT(*) AS 人数
FROM class AS c
INNER JOIN student AS s ON s.class_id = c.id
GROUP BY c.name;
一班 3 人，二班 2 人。
- 查询英语课所有成绩，显示"学生姓名、成绩"，按成绩从高到低排序。
参考答案
SELECT s.name AS 学生, sc.score AS 成绩
FROM score AS sc
INNER JOIN student AS s ON sc.student_id = s.id
WHERE sc.course_id = 2
ORDER BY sc.score DESC;
李四 92.0、张三 85.0、陈刚 73.0、王小红 60.0。赵敏没有英语成绩记录，内连接查不到她。
- 查询每个学生的英语成绩，没有记录的学生也要显示（成绩列为 NULL）。
参考答案
SELECT s.name AS 学生, sc.score AS 英语成绩
FROM student AS s
LEFT JOIN score AS sc ON sc.student_id = s.id AND sc.course_id = 2;
张三 85.0、李四 92.0、王小红 60.0、赵敏 NULL、陈刚 73.0。注意 course_id 条件必须写在 ON 里。
- 三表连接查询王小红的所有成绩，显示"课程名、老师、成绩"。
参考答案
SELECT c.name AS 课程, c.teacher AS 老师, sc.score AS 成绩
FROM score AS sc
INNER JOIN student AS s ON sc.student_id = s.id
INNER JOIN course AS c ON sc.course_id = c.id
WHERE s.name = '王小红';
数学 王老师 95.0、英语 李老师 60.0、计算机 张老师 70.0。
- 思考题：
SELECT * FROM student, course;会返回多少行？为什么？
参考答案
5 × 3 = 15 行。没有连接条件时产生笛卡尔积：student 的每一行（5 行）都会和 course 的每一行（3 行）配一次对，即使"张三 × 英语"这种组合在业务上并没有选课关系。