多表查询：多表关系、内/外/自连接、联合查询

一句话理解

表之间的关系就三种：一对一、一对多、多对多。判断方法很实在——看外键放在哪张表、要不要第三张中间表。

按这 4 步做

1. 先数“谁多”。一个部门有多名员工，一名员工只属于一个部门，所以 employee 是“多”的一方，外键 dept_id 放这里。为什么：外键放多的一方，每条员工记录只能填一个部门号，重复是天然的。
2. 一对一用于拆表。比如员工表和员工档案表，两边都只对应一条；外键放任意一方，但要加 UNIQUE，否则会退化成一堆一对多。
3. 多对多必须建中间表。学生选课，一个学生选多门课，一门课被多人选；中间表放 student_id、course_id 两个外键，联合起来当主键，重复选课才插不进去。
4. 关系定完再写查询。一对多靠 dept_id = id 连两张表；多对多要连三张表；一对一多一次 join。连接条件不是猜的，是外键指出来的。

小例

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

-- 本页练习用的建表段：在 mysqlnotes_p37 里执行一次（后面各页用各自的 pNN.sql，不复用这里）
-- department(1 研发部, 2 测试部, 3 运营部)
-- employee：小林22/研发8000/上级2，小周25/研发10000/无上级，小陈30/测试12000/上级2，小李21/无部门/NULL/上级2

CREATE TABLE department (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL
);
CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  age INT,
  dept_id INT,
  salary INT,
  manager_id INT
);
INSERT INTO department VALUES (1,'研发部'),(2,'测试部'),(3,'运营部');
INSERT INTO employee VALUES
 (1,'小林',22,1,8000,2),
 (2,'小周',25,1,10000,NULL),
 (3,'小陈',30,2,12000,2),
 (4,'小李',21,NULL,NULL,2);

-- 一对多：外键放在“多”的一方
ALTER TABLE employee
  ADD CONSTRAINT fk_emp_dept
  FOREIGN KEY (dept_id) REFERENCES department(id);

前面几段只建表灌数据；这两张表属于本页练习库 mysqlnotes_p37，后面各页用的是各自的 pNN.sql，列数和数据类型可能不同，别拿这里的 6 列结构去套别的页。最后一段给 employee.dept_id 加外键：以后 dept_id 只能填 1、2、3 或 NULL，写 99 会被拒绝。自检跑 SELECT id,name,dept_id FROM employee ORDER BY id，应得 4 行，小李的 dept_id 是 NULL——外键允许空值，不代表他有部门。

易错点

• 外键方向写反（在 department 里放 employee_id），一个部门只能记一个员工，一对多就装不下了。
• 多对多只在一边加外键，另一边的对应关系无处存放，迟早出现重复行。
• 外键列类型必须和被引用主键一致（INT 对 INT），类型不同建约束会失败。

30 秒速记

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

材料与来源

一句话理解

多表查询就是把两张表放在一起查。不写连接条件，MySQL 会把每行都和对方每行配一次，得到笛卡尔积——大部分行没有意义。

按这 4 步做

1. 先看两张表各自的规模。4 名员工 × 3 个部门 = 12 行，这就是不加条件的全部组合；行数相乘是判断“有没有漏写条件”的最快信号。
2. 再用连接条件筛掉乱配的行。员工表的 dept_id 和部门表的 id 相等，才说明这名员工属于这个部门，12 行立刻剩 3 行。
3. 连接条件写在 WHERE（隐式）或 ON（显式）都行，效果一样；差别在可读性，以及外连接时条件必须放 ON。
4. 最后记住分类：内连接、外连接、自连接、子查询。前三个是“怎么把表拼起来”，子查询是“先算一个结果再用”。

小例

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

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

-- 故意不写连接条件：4 × 3 = 12 行
SELECT e.name AS 员工, d.name AS 部门
FROM employee e, department d;

-- 补上连接条件：只剩 3 行
SELECT e.name AS 员工, d.name AS 部门
FROM employee e, department d
WHERE e.dept_id = d.id;

第一条返回 12 行，小林会分别配上研发部、测试部、运营部，明显是错配。第二条只剩 3 行：小林-研发部、小周-研发部、小陈-测试部。小李的 dept_id 是 NULL，NULL = 任何值都不成立，所以他不出现——这是内连接的正常行为，不是丢数据。

易错点

• 只写 FROM a, b 忘写 WHERE，结果行数突然变成两表行数之积；先数行数，再找条件。
• 想把无部门的员工也留下，却仍在用 WHERE 内连接，做不到；要改用 LEFT JOIN。
• 两表有同名列（都叫 name）时不用别名区分，会报 column ambiguous。

30 秒速记

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

材料与来源

一句话理解

内连接只保留两张表能对上的行：员工有部门才出现，部门有员工才出现。

按这 4 步做

1. 先确定连接条件：employee.dept_id = department.id。这是“员工属于哪个部门”的唯一依据，条件写错会得到看似正常其实错位的结果。
2. 隐式写法把两张表用逗号并列，条件放 WHERE。老教材和简写常用，表一多就容易漏条件。
3. 显式写法用 INNER JOIN ... ON，连接条件和过滤条件分家。推荐这种：ON 只写“怎么对上”，WHERE 只写“要哪些行”，读起来不容易混。
4. 写完对一下行数。4 名员工里只有 3 人有部门，内连接就该是 3 行；多出来通常是漏了条件，少了则要查 dept_id 是否为空。另外，ON 里别写 e.age < 30 这类过滤——内连接结果一样，换成外连接就会保留不该保留的行。

小例

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

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

SELECT e.name AS 员工, e.age AS 年龄, d.name AS 部门
FROM employee e
INNER JOIN department d ON e.dept_id = d.id
ORDER BY e.id;

返回 3 行：小林 22 研发部、小周 25 研发部、小陈 30 测试部。ON 负责把 dept_id 和 id 对上；ORDER BY e.id 只是让结果顺序稳定。把 INNER JOIN 换成“employee e, department d WHERE e.dept_id = d.id”，输出一模一样。 去掉 ORDER BY 再跑，行序可能变，但行集合不变：排序只影响展示。

易错点

• ON 写成 e.dept_id = d.name，类型和含义都错，却不一定报错，只会得到空结果或错配。
• 把过滤条件写进 ON 而不是 WHERE，在内连接里结果相同，但换成外连接含义就变了，别养成习惯。
• SELECT * 会带出两张表的全部列，同名列看起来重复；初学先显式写字段。
• 给表起了别名后，就不能再用原表名指字段：SELECT e.name … FROM employee e 里写 employee.name 会报 Unknown column。

30 秒速记

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

材料与来源

一句话理解

外连接在内连接的基础上，保留某一侧“配不上”的行，另一侧补 NULL。

按这 4 步做

1. 先想清楚要保住谁。要“所有员工，包括没部门的”，员工在左就用 LEFT JOIN；要“所有部门，包括没人的”，部门放右边用 RIGHT JOIN。
2. ON 里写连接条件，也可以写“只影响能不能配上”的过滤。过滤放 ON 还是 WHERE，取决于你想保留哪些左行：想保留全部员工、只给研发部的人标上部门，就把 d.name = '研发部' 放 ON，其他员工照样出现、部门列是 NULL；只要研发部员工，就把条件放 WHERE（或者干脆用内连接）。两者是不同的问题，不是对错。判断办法：把条件从 ON 挪到 WHERE，如果连本来没配上的左行也一起消失，说明这个过滤本身在筛员工，不只是筛匹配。
3. 验证时数行数：左连接保证左表每一行至少出现一次，所以结果行数不会少于左表行数；本例 department.id 是主键、一名员工最多配上一个部门，结果才恰好 4 行。若左表一行配上多个右表行，行数变多（4 行变 5 行以上）是正常放大，不是写错；变成 3 行才说明有左行被过滤掉了。
4. 统计时要分清 COUNT(*) 和 COUNT(右表列)：前者数结果行，后者只数真正匹配上的行。

小例

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

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

SELECT e.name AS 员工, d.name AS 部门
FROM employee e
LEFT JOIN department d ON e.dept_id = d.id
ORDER BY e.id;

SELECT e.name AS 员工, d.name AS 部门
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.id
ORDER BY d.id;

左连接 4 行，第 4 行是“小李 / NULL”，因为他的 dept_id 为空；本例 department.id 是主键，一名员工最多配一个部门，所以没有一对多放大，行数才正好等于左表的 4 行。右连接换方向保部门，也是 4 行，最后一行“NULL / 运营部”，因为运营部还没人。想让运营部那行出现在“部门在左”的写法里，把表序换成 department LEFT JOIN employee 即可。 右连接也可以调换表序写成 LEFT JOIN，结果相同；团队里通常统一用 LEFT，方向更一致。

易错点

• LEFT JOIN 后在 WHERE 写 d.id IS NOT NULL 会滤掉 NULL 行，等价于内连接；要专门找“没部门的员工”应写 WHERE d.id IS NULL。
• COUNT(*) 数的是结果行（4），COUNT(d.id) 数的是匹配上的部门（3）；统计“多少人有部门”必须用后者。
• MySQL 没有 FULL OUTER JOIN；要两边都保留只能 LEFT UNION RIGHT，并注意去重。

30 秒速记

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

材料与来源

一句话理解

自连接就是一张表当两张用：给同一张表起两个别名，一个当员工、一个当上级，再按 manager_id = id 连起来。

按这 4 步做

1. 先给同一张表两个身份。e 代表“我”，m 代表“我的上级”；不给别名，MySQL 分不清你要哪一行。 别名起得有含义（e 员工、m 上级），比 a、b 好排查。
2. 连接条件是 e.manager_id = m.id，即“我的上级编号 = 对方的员工编号”。这跟普通两表连接没区别，只是两张表恰好同名。
3. 顶层老板没有上级，manager_id 是 NULL。要把他留在结果里就用 LEFT JOIN；用内连接他会消失，看起来像“少了个人”。
4. 字段一律带别名前缀。同名列（两张表都叫 name、id）不带前缀会报 ambiguous，带上就清楚了。

小例

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

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

SELECT e.name AS 员工, m.name AS 上级
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id
ORDER BY e.id;

4 行：小林-小周、小周-NULL、小陈-小周、小李-小周。小周是老板，manager_id 为空，所以上级列是 NULL。把 LEFT 改成 INNER，只剩 3 行，小周整行不见——这是自连接最容易踩的坑。 把 ON 改成 e.id = m.manager_id，方向就反过来了，查的是“我的下属”，别名含义要跟着改。

易错点

• 两个别名写成同一个、或干脆不起别名，会报表名重复 / 字段 ambiguous。
• 用内连接查“所有员工及其上级”会漏掉没有上级的人；要全量必须 LEFT JOIN。
• 把 manager_id 理解成“部门编号”之类的别的字段，连接条件一错，结果会很整齐地错。

30 秒速记

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

材料与来源

一句话理解

连接横向拼列，UNION 纵向接行。UNION 去重，UNION ALL 不去重、更快。

按这 4 步做

1. 先保证两条 SELECT 列数一样、对应列类型能相容。列数不同直接报错（ERROR 1222），类型不相容会隐式转换或丢精度。 比如 INT 和 VARCHAR 合并，MySQL 会先转成同一类型再比，结果可能出乎意料。
2. 要“合并后不重复”用 UNION：它把完全相同的整行去掉，代价是多一次去重。
3. 确定不会有重复、或就是要保留重复（比如两批统计各自的行）用 UNION ALL，省掉去重开销。
4. ORDER BY 写在最后一条 SELECT 之后，作用于整个合并结果；只给第一条写排序没有意义。

小例

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

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

-- 去重：小周只留一次
SELECT name FROM employee WHERE age >= 25
UNION
SELECT name FROM employee WHERE dept_id = 1;

-- 保留重复：小周出现两次
SELECT name FROM employee WHERE age >= 25
UNION ALL
SELECT name FROM employee WHERE dept_id = 1;

第一段左边是小周、小陈，右边是小林、小周；UNION 去掉重复的小周，3 行。第二段同样的数据用 UNION ALL，4 行，小周出现两次。判断重复的依据是“整行完全相同”，不是只看某一列。 列名取第一条 SELECT 的别名，第二条写什么别名都不影响输出表头。

易错点

• UNION 只去“整行重复”；两行只要有一列不同就都保留。
• 两边列数不等报 ERROR 1222；用 SELECT * 时表结构一变就可能踩到，明确写列名。
• 拿 UNION 去拼含义不同的列（比如员工名接部门名），语义会乱；横向关联用 JOIN。
• UNION 的列名只认第一条 SELECT，第二条的别名不会生效，别指望它改表头。

30 秒速记

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

材料与来源