第八单元 · 多表连接 · 第 17 / 21 节
多表查询（一）：两张表怎么对上
建本节自己的两张表 lesson17_dept（3 行）和 lesson17_emp（4 行，其中 1 人 dept_id 为 NULL），看清一对多为什么只体现在“多”的那一方；不写连接条件时得到 4 × 3 = 12 行笛卡尔积，补上 dept_id = id 后剩 3 行；用隐式逗号和 INNER JOIN ... ON 两种写法写出同一个内连接，并解释 NULL 为什么在内连接里消失。
约 24 分钟。这一节只做三件事：把“一个部门多个员工”落在 dept_id 这一列上，亲眼看见不写条件时的 12 行垃圾结果，再用两种写法写出同样的 3 行内连接。
$ / mysql> 提示符和输出。已完成 0 / 3 步
你现在在哪个提示符
应该在 mysql>。断了就先回到学习终端（PowerShell）：
ssh root@8.148.204.152mysql-learning进去以后确认当前库：
SELECT DATABASE();+------------+
| DATABASE() |
+------------+
| itcast |
+------------+
1 row in set (0.00 sec)显示 itcast 就对了。如果是 NULL，执行 USE itcast;——它只切换当前连接用哪个库，不动数据。
第一步：关系写在“多”的那一边
三名员工分在两个部门，一个部门可以有好几个人，一个人只能属于一个部门。这种“一对多”在数据库里不是靠一句话声明，而是靠外键放在“多”的那张表上：员工表里加一列 dept_id，每条员工记录只能填一个部门号。
先建部门表（父表）。表名带 lesson17_ 前缀，是为了不碰课程自带的表，也不碰别人建的表：
SHOW TABLES LIKE 'lesson17_%';Empty set (0.01 sec)Empty set 说明这个前缀下还没有表，可以建。如果列出了两张表，说明你之前已经建过，直接跳到本节末尾「本单元验收」核对行数，不要删表重建。
CREATE TABLE lesson17_dept (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
CONSTRAINT uk_lesson17_dept_name UNIQUE (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.03 sec)INSERT INTO lesson17_dept (id, name) VALUES (1, '研发部'), (2, '测试部'), (3, '运营部');Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0SELECT * FROM lesson17_dept ORDER BY id;+----+-----------+
| id | name |
+----+-----------+
| 1 | 研发部 |
| 2 | 测试部 |
| 3 | 运营部 |
+----+-----------+
3 rows in set (0.00 sec)三行三个部门。运营部 下面还没有人——记住这一行，它是后面外连接那节课的反例来源。
再建员工表（子表），dept_id 用外键指向 lesson17_dept.id：
CREATE TABLE lesson17_emp (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED,
dept_id INT,
salary INT,
manager_id INT,
CONSTRAINT fk_lesson17_emp_dept
FOREIGN KEY (dept_id) REFERENCES lesson17_dept (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.03 sec)INSERT INTO lesson17_emp (id, name, age, dept_id, salary, manager_id) VALUES
(1, '小林', 22, 1, 8000, 2),
(2, '小周', 25, 1, 10000, NULL),
(3, '小陈', 30, 2, 12000, 2),
(4, '小李', 21, NULL, NULL, 2);Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0SELECT id, name, age, dept_id, salary, manager_id FROM lesson17_emp ORDER BY id;+----+--------+------+---------+--------+------------+
| id | name | age | dept_id | salary | manager_id |
+----+--------+------+---------+--------+------------+
| 1 | 小林 | 22 | 1 | 8000 | 2 |
| 2 | 小周 | 25 | 1 | 10000 | NULL |
| 3 | 小陈 | 30 | 2 | 12000 | 2 |
| 4 | 小李 | 21 | NULL | NULL | 2 |
+----+--------+------+---------+--------+------------+
4 rows in set (0.00 sec)读这张表，重点看两列：
dept_id：小林、小周是 1（研发部），小陈是 2（测试部），小李是NULL。NULL在这里的意思是“还没有分部门”，不是 0，也不是空字符串。manager_id：这是“我的上级是谁”，填的是另一名员工的id。小周是NULL，因为他是老板。这一列下一节做自连接时要用。
外键已经生效了：dept_id 里只能填 lesson17_dept 中真实存在的 id，填 99 会被拒绝（1452，本节「常见报错」里能亲手试一次）。这就是“一对多”的全部机关——外键放多的一方。
另外两种关系这节不建表，但形状要认得：
- 一对一：一个人一份档案。拆成两张表，外键放任意一边，但必须再加
UNIQUE，否则会退化成一堆一对多。 - 多对多：学生选课。必须建第三张中间表，表里放
student_id、course_id两个外键，联合起来当主键，同一个人重复选同一门课才插不进去。
第二步：不写连接条件，4 × 3 = 12 行
两张表放在一个 FROM 里，MySQL 会先做一件很笨的事：把左表每一行和右表每一行都配一次。4 名员工 × 3 个部门 = 12 行。
SELECT COUNT(*) AS 组合数 FROM lesson17_emp, lesson17_dept;+-----------+
| 组合数 |
+-----------+
| 12 |
+-----------+
1 row in set (0.00 sec)12 行里大部分没有意义。看一眼就知道了：
SELECT e.name AS 员工, d.name AS 部门
FROM lesson17_emp e, lesson17_dept d
ORDER BY e.id, d.id;+--------+-----------+
| 员工 | 部门 |
+--------+-----------+
| 小林 | 研发部 |
| 小林 | 测试部 |
| 小林 | 运营部 |
| 小周 | 研发部 |
| 小周 | 测试部 |
| 小周 | 运营部 |
| 小陈 | 研发部 |
| 小陈 | 测试部 |
| 小陈 | 运营部 |
| 小李 | 研发部 |
| 小李 | 测试部 |
| 小李 | 运营部 |
+--------+-----------+
12 rows in set (0.00 sec)小林被配了三次：研发部、测试部、运营部。只有“小林 + 研发部”这一行说的是真事，因为他的 dept_id 是 1。这种“每行都配一遍”的结果叫笛卡尔积。
补上连接条件，12 行立刻剩 3 行：
SELECT e.name AS 员工, d.name AS 部门
FROM lesson17_emp e, lesson17_dept d
WHERE e.dept_id = d.id
ORDER BY e.id;+--------+-----------+
| 员工 | 部门 |
+--------+-----------+
| 小林 | 研发部 |
| 小周 | 研发部 |
| 小陈 | 测试部 |
+--------+-----------+
3 rows in set (0.00 sec)三行：小林-研发部、小周-研发部、小陈-测试部。小李不见了——他的 dept_id 是 NULL，NULL = 任何值 的结果都是“未知”，不是“真”，所以配不上。这只是内连接的定义（只留配得上的行），不是数据丢了；要保留他得用外连接，下一节讲。
先数行数，再找条件。 结果突然变成两张表行数的乘积，第一反应就应该是“我漏了连接条件”。
多表查询一共四类，先记住分工，别混：
| 类别 | 干什么 | 本节/下节 |
|---|---|---|
| 内连接 | 只保留两张表都配得上的行 | 本节第三步 |
| 外连接 | 保留某一侧配不上的行，另一侧补 NULL | 第 18 节 |
| 自连接 | 一张表当两张用（员工和上级） | 第 18 节 |
| 子查询 | 先算一个结果，再拿它当条件 | 第九单元 |
第三步：内连接的两种写法，结果一模一样
第一种是刚才那种：两张表用逗号并列，条件写在 WHERE。叫隐式内连接。
SELECT e.name AS 员工, e.age AS 年龄, d.name AS 部门
FROM lesson17_emp e, lesson17_dept d
WHERE e.dept_id = d.id
ORDER BY e.id;+--------+--------+-----------+
| 员工 | 年龄 | 部门 |
+--------+--------+-----------+
| 小林 | 22 | 研发部 |
| 小周 | 25 | 研发部 |
| 小陈 | 30 | 测试部 |
+--------+--------+-----------+
3 rows in set (0.00 sec)第二种把“怎么对上”单独写进 ON，叫显式内连接：
SELECT e.name AS 员工, e.age AS 年龄, d.name AS 部门
FROM lesson17_emp e
INNER JOIN lesson17_dept d ON e.dept_id = d.id
ORDER BY e.id;+--------+--------+-----------+
| 员工 | 年龄 | 部门 |
+--------+--------+-----------+
| 小林 | 22 | 研发部 |
| 小周 | 25 | 研发部 |
| 小陈 | 30 | 测试部 |
+--------+--------+-----------+
3 rows in set (0.00 sec)两段输出逐行相同。区别不在结果，在读法：
ON只回答“这两张表凭什么对上”——e.dept_id = d.id；WHERE只回答“我要哪些行”——比如再加WHERE e.age >= 25。
表一多，WHERE 里会堆一堆条件，很容易把连接条件和过滤条件搅在一起，所以推荐显式写法。INNER JOIN 的 INNER 可以省略，JOIN 默认就是内连接。
最后核对一下行数：4 名员工里 3 人有部门，内连接就该是 3 行。多出来通常是漏了条件；少了，先查 dept_id 是不是 NULL。
常见报错怎么办
| 看到 | 意思 | 怎么办 |
|---|---|---|
1052 Column ... in field list is ambiguous | 两张表都有这一列，没说是哪张 | 给列加表别名前缀：e.name |
1146 Table ... doesn't exist | 表名拼错，或建在了别的库 | SHOW TABLES LIKE 'lesson17_%'; 核对表名，再 SELECT DATABASE(); |
1452 Cannot add or update a child row | 外键列填了父表不存在的值 | 先查父表 SELECT * FROM lesson17_dept ORDER BY id;，填真实存在的 id |
说明
SELECT name FROM lesson17_emp e JOIN lesson17_dept d ON e.dept_id = d.id;ERROR 1052 (23000): Column 'name' in field list is ambiguous员工表和部门表都有 name，MySQL 不知道你要哪一个，直接拒绝。写成 e.name（员工名）或 d.name（部门名）就清楚了。
说明
SELECT * FROM lesson17_employes;ERROR 1146 (42S02): Table 'itcast.lesson17_employes' doesn't exist1146 只说明“这个名字的表不存在”，不代表表被删了。SHOW TABLES LIKE 'lesson17_%'; 看一眼真实表名，lesson17_emp 没有 loyes 这一段。
说明
INSERT INTO lesson17_emp (id, name, dept_id) VALUES (9, '小测试', 99);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`itcast`.`lesson17_emp`, CONSTRAINT `fk_lesson17_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `lesson17_dept` (`id`))99 在 lesson17_dept 里不存在，外键把这一行挡在门外。这条插入不会留下任何数据；想加人，先加部门，或者把 dept_id 填成 NULL（表示还没分部门）。
练习
先自己做，再展开提示和对答案。前三题动手，后两题只问概念。
1. 每个有人的部门各有几个人、平均工资多少？按部门号排序。
说明
两张表连起来，GROUP BY 按部门分组，COUNT(*) 数人，AVG(e.salary) 算平均。排序用 d.id，不要用中文部门名——那样顺序要依赖排序规则。
说明
SELECT d.id AS 部门号, d.name AS 部门, COUNT(*) AS 人数, AVG(e.salary) AS 平均工资
FROM lesson17_emp e
JOIN lesson17_dept d ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY d.id;+-----------+-----------+--------+--------------+
| 部门号 | 部门 | 人数 | 平均工资 |
+-----------+-----------+--------+--------------+
| 1 | 研发部 | 2 | 9000.0000 |
| 2 | 测试部 | 1 | 12000.0000 |
+-----------+-----------+--------+--------------+
2 rows in set (0.01 sec)只有两个部门有人，所以两行；运营部没人，内连接里根本不会出现。平均工资后面那一串小数是 AVG 的默认精度（4 位），不是数据错了。
说明
忘了 GROUP BY，COUNT(*) 会把 3 行数成 1 行；或者把 d.name 放进 SELECT 但没进 GROUP BY，在 ONLY_FULL_GROUP_BY 下报 1055。
2. 年龄 25 岁及以上、且属于研发部的员工是谁？
说明
连接条件写 ON，年龄和部门名是过滤条件，写 WHERE。
说明
SELECT e.name AS 员工, e.age AS 年龄, d.name AS 部门
FROM lesson17_emp e
JOIN lesson17_dept d ON e.dept_id = d.id
WHERE e.age >= 25 AND d.name = '研发部';+--------+--------+-----------+
| 员工 | 年龄 | 部门 |
+--------+--------+-----------+
| 小周 | 25 | 研发部 |
+--------+--------+-----------+
1 row in set (0.00 sec)只有小周。小林 22 岁被年龄条件筛掉；小陈在测试部，被部门条件筛掉。
说明
把 e.age >= 25 写进 ON。内连接里结果碰巧一样，但习惯一旦带到外连接就会变意思——过滤条件属于 WHERE。
**3. 用隐式写法重写第三步的查询（不要 INNER JOIN），行数应该还是 3。**
说明
两张表用逗号并列，连接条件从 ON 挪到 WHERE。
说明
SELECT e.name AS 员工, e.age AS 年龄, d.name AS 部门
FROM lesson17_emp e, lesson17_dept d
WHERE e.dept_id = d.id
ORDER BY e.id;+--------+--------+-----------+
| 员工 | 年龄 | 部门 |
+--------+--------+-----------+
| 小林 | 22 | 研发部 |
| 小周 | 25 | 研发部 |
| 小陈 | 30 | 测试部 |
+--------+--------+-----------+
3 rows in set (0.00 sec)还是 3 行。两种写法只是把同一个条件放在不同位置，MySQL 执行的是同一件事。
说明
把逗号写成 JOIN 却仍把条件放 WHERE——语法上能跑（JOIN 不带 ON 是允许的），但读的人会以为你漏了连接条件。
4. 小李为什么不出现在内连接结果里？如果业务要求“所有员工都要列出来”，这条 SQL 还够用吗？
说明
小李的 dept_id 是 NULL。内连接要求连接条件为“真”，而 NULL = 1 的结果是“未知”，所以他配不上任何部门，整行被丢掉。业务要“所有员工”时，内连接不够，得换成保留左表全部行的外连接（下一节第一个例子就是这条查询）。
5. 为什么外键要放在员工表，而不是在部门表里加一列“员工编号”？
说明
一个部门有多个员工，部门表一行只能存一个值，塞不下第二个员工。外键放在“多”的一方（员工表），每条员工记录填一个部门号，重复是天然的——研发部有两名员工，就是两行 dept_id = 1。反过来放，要么一个部门只能有一个人，要么得往一格塞逗号分隔的编号，那就不是关系型写法了。
本单元验收
做到下面 5 条，这一节就完成了。每条都给出你应该看到的真实结果。
SHOW TABLES LIKE 'lesson17_%';正好两张：lesson17_dept、lesson17_emp。SELECT COUNT(*) FROM lesson17_emp;是4，SELECT COUNT(dept_id) FROM lesson17_emp;是3——差的那一行是小李。- 不写条件时
SELECT COUNT(*) FROM lesson17_emp, lesson17_dept;是12；补上WHERE e.dept_id = d.id后是3。 - 隐式写法和
INNER JOIN ... ON的输出逐行相同，都是小林、小周、小陈三行。 - 能说出小李为什么被内连接丢掉，以及
e.name前面那个别名不写会报1052。
做到这里
做到这里可以停。你现在能看懂“一对多”为什么靠外键放在多的一方，能一眼认出漏写连接条件造成的笛卡尔积，也能用两种写法写出同一个内连接。
下一节换两张自己的表（lesson18_*，多一个没有员工的部门、多一名员工），解决这一节留下的问题：小李这种配不上的行怎么保留，以及同一张表怎么当两张用。
来源与边界
本节对应 BV1Kr4y1i7ru 平台 part 37「37. 基础-多表查询-多表关系介绍」、part 38「38. 基础-多表查询-概述」、part 39「39. 基础-多表查询-内连接」（P37–P39，在 P01–P50 范围内），主题与同版讲义《MySQL-基础篇》5.1（p69–72）、5.2（p72–75）、5.3（p75–77）对应；讲义为公开镜像，仅作主题与字段语义核对，本节未复制讲义正文。
无逐字稿，本节不声称听过讲解、不引用原话、不写时间戳。讲义这几节用的示例表是 emp（17 行）与 dept（6 行）；本节换成自己建的两张表——lesson17_dept 3 行、lesson17_emp 4 行——列名与语义对齐（id / name / age / dept_id / salary / manager_id，其中讲义 emp.sal 是 DECIMAL(7,2)，本节用 INT 表示元、取整），行数据全部原创，便于口算 4 × 3 = 12。表名、数据、查询与练习均为本教程原创。
本节每段 text 输出都在本机隔离的 MySQL 8.0.46 实例上逐条实跑（与学习服务器同版本号，不是同一台机器）；0.00 sec 是测试机耗时，你屏幕上可能是 0.01 sec，行列内容一致。页面不含 DROP / TRUNCATE / 整库重置语句；重跑靠 SHOW TABLES LIKE 'lesson17_%'; 和行数核对，不需要删表。