多表查询练习与本章小结

一句话理解

把前几页连起来用：先判断“要哪些行”，再选内连接还是外连接，最后检查有没有重复、有没有漏掉无部门的人。

按这 3 步做

1. 逐题先写“表 + 连接条件 + 过滤条件”三行草稿。表是 employee 和 department，连接条件是 e.dept_id = d.id，过滤条件才决定内连接还是外连接。
2. 题目说“所有员工，没有部门也要”就选 LEFT JOIN；说“有员工的部门”就用内连接再去重。两类题的外连接方向相反，先抓关键词。
3. 写完数行数对答案：4 名员工里 3 人有部门，3 个部门里 2 个有人。题1 和题2 都是内连接，区别是题2 多一个年龄过滤，所以题2 行数更少（1 行对 3 行）；两题行集合不同，不能互相对照。

小例

先准备本集练习数据：p48.sql · 练习库说明

-- 表：department(1 研发部, 2 测试部, 3 运营部)
-- employee：小林22/研发8000/上级2，小周25/研发10000/无上级，小陈30/测试12000/上级2，小李21/无部门/NULL/上级2
-- 建表与插入语句见本页练习脚本 p48.sql（独立练习库，跑一次就有这两张表）

-- 题1：所有员工及其部门（隐式内连接），3 行
SELECT e.name AS 员工, e.age AS 年龄, d.name AS 部门
FROM employee e, department d
WHERE e.dept_id = d.id
ORDER BY e.id;

-- 题2：年龄小于 25 的员工及其部门（显式内连接；25 是本页自定，原课是 age < 30），1 行
SELECT e.name AS 员工, e.age AS 年龄, d.name AS 部门
FROM employee e
INNER JOIN department d ON e.dept_id = d.id
WHERE e.age < 25
ORDER BY e.id;

-- 题3：有员工的部门（内连接 + 去重）
SELECT DISTINCT d.id, d.name
FROM employee e, department d
WHERE e.dept_id = d.id;

-- 题4：所有员工及其部门，没部门也要出现
SELECT e.name AS 员工, e.age AS 年龄, d.name AS 部门
FROM employee e
LEFT JOIN department d ON e.dept_id = d.id
ORDER BY e.id;

题1 是隐式内连接，4 名员工里只有 3 人有部门（小李 dept_id 为空，配不上），得到 3 行：小林、小周、小陈。题2 在本页是「显式内连接 + age < 25」：年龄小于 25 的有小林 22 和小李 21，小李没部门被内连接去掉，所以只剩 1 行小林——这就是题2 比题1 少的原因，两题行集合不同，不能互相对照。题3 得到 2 行：1 研发部、2 测试部；运营部没人所以不出现；DISTINCT 是因为研发部有两名员工，不去重会重复两行。题4 得到 4 行，小李的部门列是 NULL。 题2 里的 age < 25 是过滤员工，放在 WHERE；如果改成左连接再把它挪进 ON，年龄大的员工仍会出现，只是部门为 NULL，行数就多了。说明：题2 是本页自己的原创练习（显式内连接 + age < 25），不是视频原题逐字；原课第 2 题是「年龄小于 30 岁」+ 显式内连接（讲义 p85 第 2 题），第 1 题才是隐式连接且不带年龄条件，别混着对答案。

易错点

• 题3 忘了 DISTINCT，部门会按员工人数重复。
• 题4 “所有员工”却用了内连接，会漏掉小李且不报错。
• 把 e.age 之类的员工过滤写进 ON，左连接仍保留全部员工，含义容易混：连接条件放 ON，过滤员工放 WHERE。

30 秒速记

自测（3 题，先答再展开）

材料与来源

一句话理解

先把需求拆成单句验证：分组统计、相关子查询各一条；薪资档位用 salgrade 的区间连接，再把部门连上就是三表。

按这 4 步做

1. 统计类先定「以谁为基准」。要每个部门都出现（包括没人），就以 department 为左表 LEFT JOIN employee，别反过来。
2. 计数时用 COUNT(e.id) 而不是 COUNT(*)。左连接会给没人部门补一行 NULL，COUNT(*) 会把它算成 1 人，COUNT(e.id) 才是 0。
3. 相关子查询里外层的值会传进内层。e.salary > (SELECT AVG(x.salary) ... WHERE x.dept_id = e.dept_id) 读作「高于本部门平均」，内层每行重算。
4. 查薪资档次不用等值连接：e.salary BETWEEN s.losal AND s.hisal 就是一条非等值连接；要部门名就把 department 再连一次，变成三表。

小例

先准备本集练习数据：p49.sql · 练习库说明

-- 表：department(1 研发部, 2 测试部, 3 运营部)
-- employee：小林22/研发8000/上级2，小周25/研发10000/无上级，小陈30/测试12000/上级2，小李21/无部门/NULL/上级2
-- 建表与插入语句见本页练习脚本 p49.sql（独立练习库 mysqlnotes_p49，跑一次就有这两张表）

-- 每部门人数与平均工资（运营部也要出现）
SELECT d.name AS 部门, COUNT(e.id) AS 人数, AVG(e.salary) AS 平均工资
FROM department d
LEFT JOIN employee e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY d.id;

-- 高于本部门平均工资的人（相关子查询）
SELECT e.name AS 员工, e.dept_id AS 部门号, e.salary AS 工资
FROM employee e
WHERE e.salary > (
  SELECT AVG(x.salary) FROM employee x WHERE x.dept_id = e.dept_id
);

第一段 3 行：研发部 2 人 / 9000、测试部 1 人 / 12000、运营部 0 人 / NULL。第二段研发部平均 9000，小周 10000 入选；测试部只有小陈，12000 不大于 12000；小李没有部门，内层算不出平均（NULL），不入选。共 1 行。 反连接写法：SELECT d.id, d.name FROM department d LEFT JOIN employee e ON e.dept_id = d.id WHERE e.id IS NULL，结果只有 3 运营部。这三个数字就是核对后面所有统计题的基准，先记住它们。

-- salgrade 是本页原创的 4 档小表（grade, losal, hisal），不是讲义那 8 档
SELECT e.name AS 员工, e.salary AS 工资, s.grade AS 档位
FROM employee e
JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal
ORDER BY e.id;

SELECT e.name AS 员工, d.name AS 部门, s.grade AS 档位
FROM employee e
JOIN department d ON e.dept_id = d.id
JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal
ORDER BY e.id;

易错点

• LEFT JOIN 后用 COUNT(*) 统计人数，会把「没人」的部门算成 1。
• 相关子查询忘写 x.dept_id = e.dept_id，就退化成全公司平均，结果看似合理但是错的。
• 区间连接漏了端点（写成 salary > losal AND salary < hisal），正好等于档位边界的工资会掉档。
• 以为 NULL 工资会落进第 1 档：NULL 参与 BETWEEN 得 unknown，不会匹配任何档。

30 秒速记

自测（3 题，先答再展开）

材料与来源

这一集在讲什么

多表查询的决策只有三步：先看关系定连接条件，再看要不要保留配不上的行，最后看要不要嵌套。

要点（3 条）

1. 关系决定外键位置：一对多外键在多的一方；多对多要中间表；一对一外键加 UNIQUE。
2. 连接先问“要不要保留全部”：只留匹配用内连接；保留一侧用左/右外连接；同表两个身份用自连接。
3. 结果怎么合：合并行用 UNION（去重）或 UNION ALL；拿内层结果当条件或当表，就按返回形状选标量 / 列 / 行 / 表子查询。

30 秒回忆

想再练一下（2 题，选做）

材料与来源