MySQL不看视频 · SSH 实操

第八单元 · 多表连接 · 第 18 / 21 节

多表查询（二）：外连接、自连接、UNION

换一套自己的表 lesson18_dept（5 行，2 个空部门）和 lesson18_emp（5 行，1 人无部门、1 人无上级），先看清 LEFT JOIN 保留小李、RIGHT JOIN 保留两个空部门，再验证过滤条件放 ON 会留 5 行、放 WHERE 只剩 2 行；用自连接列出所有员工及其上级（内连接会漏掉老板）；最后用 UNION / UNION ALL 把两次查询竖直接起来，并复现 1222 列数不等。

3 个步骤 · 进度只存本机

约 26 分钟。上一节里小李因为 dept_id 是 NULL 被内连接丢掉了；这一节先把他捞回来（外连接），再让同一张表扮演两个角色（自连接），最后把两次查询的结果竖直接起来（UNION）。

SSH / Linux 终端MySQL 终端输出示例（只读）复制按钮只复制命令，不含 $ / mysql> 提示符和输出。

已完成 0 / 3 步

你现在在哪个提示符

应该在 mysql>。断了就先回到学习终端（PowerShell）：

SSH / Linux 终端bash
ssh root@8.148.204.152
SSH / Linux 终端bash
mysql-learning

进去以后确认当前库：

MySQL 终端sql
SELECT DATABASE();
输出示例（只读，不要粘贴执行）text不可复制
+------------+
| DATABASE() |
+------------+
| itcast     |
+------------+
1 row in set (0.00 sec)

显示 itcast 就对了。如果是 NULL，执行 USE itcast;——它只切换当前连接用哪个库，不动数据。

第一步：外连接——配不上的行也要留下

本节用自己的一套表，前缀 lesson18_。上一节的 lesson17_* 不参与，这一节可以单独重做。

MySQL 终端sql
SHOW TABLES LIKE 'lesson18_%';
输出示例（只读，不要粘贴执行）text不可复制
Empty set (0.00 sec)

Empty set 说明可以建。已经有表就跳到本步末尾核对行数，不要删表。

MySQL 终端sql
CREATE TABLE lesson18_dept (
  id   INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT uk_lesson18_dept_name UNIQUE (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
输出示例（只读，不要粘贴执行）text不可复制
Query OK, 0 rows affected (0.04 sec)
MySQL 终端sql
INSERT INTO lesson18_dept (id, name) VALUES (1, '研发部'), (2, '测试部'), (3, '运营部'), (4, '财务部'), (5, '人事部');
输出示例（只读，不要粘贴执行）text不可复制
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
MySQL 终端sql
CREATE TABLE lesson18_emp (
  id         INT PRIMARY KEY,
  name       VARCHAR(20) NOT NULL,
  age        TINYINT UNSIGNED,
  dept_id    INT,
  salary     INT,
  manager_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
输出示例（只读，不要粘贴执行）text不可复制
Query OK, 0 rows affected (0.03 sec)
MySQL 终端sql
INSERT INTO lesson18_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),
  (5, '小赵', 28, 3, 9000, 1);
输出示例（只读，不要粘贴执行）text不可复制
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
MySQL 终端sql
SELECT id, name, age, dept_id, salary, manager_id FROM lesson18_emp ORDER BY id;
输出示例（只读，不要粘贴执行）text不可复制
+----+--------+------+---------+--------+------------+
| 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 |
|  5 | 小赵   |   28 |       3 |   9000 |          1 |
+----+--------+------+---------+--------+------------+
5 rows in set (0.00 sec)

五名员工、五个部门。和上一节比，多了小赵（运营部）和两个空部门：财务部、人事部。这几行就是外连接的用武之地——内连接只会返回 4 行，小李和两个空部门都不见了。

先看内连接：

MySQL 终端sql
SELECT e.name AS 员工, d.name AS 部门
FROM lesson18_emp e
JOIN lesson18_dept d ON e.dept_id = d.id
ORDER BY e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+-----------+
| 员工   | 部门      |
+--------+-----------+
| 小林   | 研发部    |
| 小周   | 研发部    |
| 小陈   | 测试部    |
| 小赵   | 运营部    |
+--------+-----------+
4 rows in set (0.00 sec)

4 行。现在把 JOIN 换成 LEFT JOIN——“左表每一行都必须留下”：

MySQL 终端sql
SELECT e.name AS 员工, d.name AS 部门
FROM lesson18_emp e
LEFT JOIN lesson18_dept d ON e.dept_id = d.id
ORDER BY e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+-----------+
| 员工   | 部门      |
+--------+-----------+
| 小林   | 研发部    |
| 小周   | 研发部    |
| 小陈   | 测试部    |
| 小李   | NULL      |
| 小赵   | 运营部    |
+--------+-----------+
5 rows in set (0.00 sec)

5 行，多出最后一行：小李的部门列是 NULL。这不是查错了，是外连接补出来的——左表有小李，右表找不到对应部门，右表的列就填 NULL。

方向反过来，RIGHT JOIN 保右表（部门）：

MySQL 终端sql
SELECT e.name AS 员工, d.name AS 部门
FROM lesson18_emp e
RIGHT JOIN lesson18_dept d ON e.dept_id = d.id
ORDER BY d.id, e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+-----------+
| 员工   | 部门      |
+--------+-----------+
| 小林   | 研发部    |
| 小周   | 研发部    |
| 小陈   | 测试部    |
| 小赵   | 运营部    |
| NULL   | 财务部    |
| NULL   | 人事部    |
+--------+-----------+
6 rows in set (0.00 sec)

6 行：4 行正常匹配 + 财务部、人事部 两行员工列是 NULL。所以选哪个方向只问一句：你要保住谁？ 要“所有员工”就用左表放员工；要“所有部门”就用右表放部门（或者把部门放左边用 LEFT JOIN，结果一样、读起来更顺）。

外连接还有一个容易搞错的地方：过滤条件放 ON 还是 WHERE，结果不同。想要“所有员工都列出来，只给研发部的人标上部门名”：

MySQL 终端sql
SELECT e.name AS 员工, d.name AS 部门
FROM lesson18_emp e
LEFT JOIN lesson18_dept d ON e.dept_id = d.id AND d.name = '研发部'
ORDER BY e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+-----------+
| 员工   | 部门      |
+--------+-----------+
| 小林   | 研发部    |
| 小周   | 研发部    |
| 小陈   | NULL      |
| 小李   | NULL      |
| 小赵   | NULL      |
+--------+-----------+
5 rows in set (0.00 sec)

还是 5 行：条件只是“能不能配上”，配不上的员工照样留下，部门列是 NULL。把同一个条件挪到 WHERE：

MySQL 终端sql
SELECT e.name AS 员工, d.name AS 部门
FROM lesson18_emp e
LEFT JOIN lesson18_dept d ON e.dept_id = d.id
WHERE d.name = '研发部'
ORDER BY e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+-----------+
| 员工   | 部门      |
+--------+-----------+
| 小林   | 研发部    |
| 小周   | 研发部    |
+--------+-----------+
2 rows in set (0.00 sec)

只剩 2 行——WHERE 是在结果上筛行，d.name 为 NULL 的行一律被筛掉，外连接等于退化成了内连接。规律很好记：**连接条件放 ON，筛行放 WHERE**。

统计时还要分清两个 COUNT：

MySQL 终端sql
SELECT COUNT(*) AS 结果行数, COUNT(d.id) AS 真正匹配上
FROM lesson18_emp e
LEFT JOIN lesson18_dept d ON e.dept_id = d.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------------+-----------------+
| 结果行数     | 真正匹配上      |
+--------------+-----------------+
|            5 |               4 |
+--------------+-----------------+
1 row in set (0.00 sec)

COUNT(*) 数结果行（5），COUNT(d.id) 只数右表真有值的行（4）。做“每个部门几个人”这类统计时，用错就会把没人也算成 1。

第二步：自连接——一张表当两张用

员工表里的 manager_id 存的是“我上级的 id”，上级也是员工，也在这张表里。要同时看到“我”和“我的上级”，就得把同一张表请出来两次，各给一个别名：

MySQL 终端sql
SELECT e.name AS 员工, m.name AS 上级
FROM lesson18_emp e
LEFT JOIN lesson18_emp m ON e.manager_id = m.id
ORDER BY e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+--------+
| 员工   | 上级   |
+--------+--------+
| 小林   | 小周   |
| 小周   | NULL   |
| 小陈   | 小周   |
| 小李   | 小周   |
| 小赵   | 小林   |
+--------+--------+
5 rows in set (0.00 sec)

5 行。e 是“我”，m 是“我的上级”，连接条件 e.manager_id = m.id 就是“我的上级编号 = 对方的员工编号”。小周没有上级，manager_id 是 NULL，配不上任何一行，靠 LEFT JOIN 才留下来。

换成内连接看看少了谁：

MySQL 终端sql
SELECT e.name AS 员工, m.name AS 上级
FROM lesson18_emp e
JOIN lesson18_emp m ON e.manager_id = m.id
ORDER BY e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+--------+
| 员工   | 上级   |
+--------+--------+
| 小林   | 小周   |
| 小陈   | 小周   |
| 小李   | 小周   |
| 小赵   | 小林   |
+--------+--------+
4 rows in set (0.00 sec)

4 行，小周整行消失。这是自连接最常见的坑：查“所有员工及其上级”必须用外连接，否则没有上级的老板会被悄悄丢掉。

把条件方向反过来，同一个写法查的就是“我的下属”：

MySQL 终端sql
SELECT m.name AS 上级, e.name AS 下属
FROM lesson18_emp e
JOIN lesson18_emp m ON e.manager_id = m.id
ORDER BY m.id, e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+--------+
| 上级   | 下属   |
+--------+--------+
| 小林   | 小赵   |
| 小周   | 小林   |
| 小周   | 小陈   |
| 小周   | 小李   |
+--------+--------+
4 rows in set (0.00 sec)

小周有三个直接下属（小林、小陈、小李），小林有一个（小赵）。别名含义跟着条件一起换，别只改一半。

第三步：UNION——把两次查询竖直接起来

连接是横向拼列（列变多），UNION 是纵向接行（行变多）。先看两段各自的结果：

MySQL 终端sql
SELECT name AS 姓名 FROM lesson18_emp WHERE age >= 25 ORDER BY 姓名;
输出示例（只读，不要粘贴执行）text不可复制
+--------+
| 姓名   |
+--------+
| 小周   |
| 小赵   |
| 小陈   |
+--------+
3 rows in set (0.00 sec)
MySQL 终端sql
SELECT name AS 姓名 FROM lesson18_emp WHERE dept_id = 1 ORDER BY 姓名;
输出示例（只读，不要粘贴执行）text不可复制
+--------+
| 姓名   |
+--------+
| 小周   |
| 小林   |
+--------+
2 rows in set (0.00 sec)

两段都是“一列姓名”。用 UNION 接起来，重复的小周只留一次。这里加了 ORDER BY 姓名 只为让顺序可复现——它按本表排序规则（utf8mb4_0900_ai_ci）排，不是拼音，顺序也不影响“哪些名字在结果里”：

MySQL 终端sql
SELECT name AS 姓名 FROM lesson18_emp WHERE age >= 25
UNION
SELECT name AS 姓名 FROM lesson18_emp WHERE dept_id = 1
ORDER BY 姓名;
输出示例（只读，不要粘贴执行）text不可复制
+--------+
| 姓名   |
+--------+
| 小周   |
| 小林   |
| 小赵   |
| 小陈   |
+--------+
4 rows in set (0.00 sec)

4 行。换成 UNION ALL，小周出现两次：

MySQL 终端sql
SELECT name AS 姓名 FROM lesson18_emp WHERE age >= 25
UNION ALL
SELECT name AS 姓名 FROM lesson18_emp WHERE dept_id = 1
ORDER BY 姓名;
输出示例（只读，不要粘贴执行）text不可复制
+--------+
| 姓名   |
+--------+
| 小周   |
| 小周   |
| 小林   |
| 小赵   |
| 小陈   |
+--------+
5 rows in set (0.00 sec)

5 行。区别只有一个：UNION 对合并后的整行去重，UNION ALL 原样保留、也不花去重的开销。判断“重复”看的是整行完全相同，不是只看姓名这一列——如果第二段还查出年龄，同样的姓名也会因为年龄不同而都留下。

两条规矩：

  1. 两边列数必须一样，否则报 1222（下面「常见报错」里有一条真的）；
  2. ORDER BY 只能写在最后一条 SELECT 之后，作用于整个合并结果；要给某一段单独排序必须写成 (SELECT … ORDER BY …)，把 ORDER BY 直接写在前一段后面会报 1064 语法错误。

列名只取第一条 SELECT 的别名，第二条写什么别名都不影响表头。

常见报错怎么办

看到意思怎么办
1052 Column ... is ambiguous自连接两个别名，列没说是哪一个每个列都带别名：e.name、m.name
1222 The used SELECT statements have a different number of columnsUNION 两边列数不一样两边都明确写列名，数目对齐
说明
MySQL 终端sql
SELECT name, name FROM lesson18_emp e JOIN lesson18_emp m ON e.manager_id = m.id;
输出示例（只读，不要粘贴执行）text不可复制
ERROR 1052 (23000): Column 'name' in field list is ambiguous

e 和 m 都是 lesson18_emp，name 属于哪一边说不清，1052。写成 e.name（员工）、m.name（上级）就行。

说明
MySQL 终端sql
SELECT name, age FROM lesson18_emp WHERE age >= 25
UNION
SELECT name FROM lesson18_dept;
输出示例（只读，不要粘贴执行）text不可复制
ERROR 1222 (21000): The used SELECT statements have a different number of columns

左边两列、右边一列，1222。UNION 要求“形状一样才能接”，列数、对应列的类型都要能对上。

练习

先自己做，再展开提示和对答案。前三题动手，第四题动手加解释，最后一题只问概念。

1. 每个部门各有几个人？没人也要显示 0 人。

说明

部门要全部留下，所以部门在左、用 LEFT JOIN；数人用 COUNT(e.id)，不要用 COUNT(*)。

说明
MySQL 终端sql
SELECT d.id AS 部门号, d.name AS 部门, COUNT(e.id) AS 人数
FROM lesson18_dept d
LEFT JOIN lesson18_emp e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY d.id;
输出示例（只读，不要粘贴执行）text不可复制
+-----------+-----------+--------+
| 部门号    | 部门      | 人数   |
+-----------+-----------+--------+
|         1 | 研发部    |      2 |
|         2 | 测试部    |      1 |
|         3 | 运营部    |      1 |
|         4 | 财务部    |      0 |
|         5 | 人事部    |      0 |
+-----------+-----------+--------+
5 rows in set (0.00 sec)

五行为什么？因为左表是部门，五个部门都在；COUNT(e.id) 数的是“右表真有值”的行，财务部和人事部是 0。用 COUNT(*) 会得到 1，因为左连接给它们各补了一行 NULL。

说明

把部门放在右表却仍写 LEFT JOIN，结果变成“所有员工 + 部门”，没人部门又不见了；或者忘写 GROUP BY，五行被压成一行。

2. 哪几个部门一个人都没有？

说明

还是“部门全部留下 + 左连接”，然后只留右表没配上的行：WHERE e.id IS NULL。这种写法叫反连接。

说明
MySQL 终端sql
SELECT d.id AS 部门号, d.name AS 部门
FROM lesson18_dept d
LEFT JOIN lesson18_emp e ON e.dept_id = d.id
WHERE e.id IS NULL
ORDER BY d.id;
输出示例（只读，不要粘贴执行）text不可复制
+-----------+-----------+
| 部门号    | 部门      |
+-----------+-----------+
|         4 | 财务部    |
|         5 | 人事部    |
+-----------+-----------+
2 rows in set (0.00 sec)

财务部、人事部。e.id IS NULL 的意思是“这一行没有对应的员工”，不是“员工 id 是空的”——补出来的行右表所有列都是 NULL。

说明

写成 WHERE e.dept_id IS NULL：左连接补出来的行 dept_id 确实也是 NULL，能跑出同样结果，但语义是“员工没填部门”，换一张表就会错；判“有没有匹配上”要用右表主键。

3. 列出所有员工和他们的上级姓名，没有上级的也要出现。

说明

自连接，同一张表两个别名，要全量所以用 LEFT JOIN。

说明
MySQL 终端sql
SELECT e.name AS 员工, m.name AS 上级
FROM lesson18_emp e
LEFT JOIN lesson18_emp m ON e.manager_id = m.id
ORDER BY e.id;
输出示例（只读，不要粘贴执行）text不可复制
+--------+--------+
| 员工   | 上级   |
+--------+--------+
| 小林   | 小周   |
| 小周   | NULL   |
| 小陈   | 小周   |
| 小李   | 小周   |
| 小赵   | 小林   |
+--------+--------+
5 rows in set (0.00 sec)

5 行，小周的上级是 NULL。用内连接会只剩 4 行。

说明

两个别名起成同一个名字（FROM lesson18_emp e JOIN lesson18_emp e），会报“表名重复”；或者条件写成 e.id = m.manager_id，查出来的是“我的下属”，不是“我的上级”。

**4. 年龄 ≥ 25 或工资 ≥ 10000 的员工姓名，用 UNION 写一遍，再用 UNION ALL 写一遍，比较行数。**

说明

两段都从 lesson18_emp 查 name，条件分别是 age >= 25 和 salary >= 10000。小周同时满足两个条件。

说明
MySQL 终端sql
SELECT name AS 姓名 FROM lesson18_emp WHERE age >= 25
UNION
SELECT name AS 姓名 FROM lesson18_emp WHERE salary >= 10000
ORDER BY 姓名;
输出示例（只读，不要粘贴执行）text不可复制
+--------+
| 姓名   |
+--------+
| 小周   |
| 小赵   |
| 小陈   |
+--------+
3 rows in set (0.00 sec)
MySQL 终端sql
SELECT name AS 姓名 FROM lesson18_emp WHERE age >= 25
UNION ALL
SELECT name AS 姓名 FROM lesson18_emp WHERE salary >= 10000
ORDER BY 姓名;
输出示例（只读，不要粘贴执行）text不可复制
+--------+
| 姓名   |
+--------+
| 小周   |
| 小周   |
| 小赵   |
| 小陈   |
| 小陈   |
+--------+
5 rows in set (0.00 sec)

UNION 3 行（小周只留一次），UNION ALL 5 行（小周两次）。这类“同一张表两个条件取并集”其实一条 WHERE age >= 25 OR salary >= 10000 就够，UNION 更适合两段来自不同表、或各自要排序/聚合的场合。

说明

以为 UNION 会按姓名去重——它去的是整行；一旦两段都 SELECT *，同一个人的年龄、工资都相同才算重复，加一列就会变。

**5. 为什么说 LEFT JOIN 后面接 WHERE d.id IS NULL 变成了“反连接”？它和内连接是什么关系？**

说明

LEFT JOIN 先把左表全部留下、配不上的右表列补 NULL；WHERE d.id IS NULL 只保留这些“没配上”的行，于是结果正好是“左表里有、右表里没有”的那部分——这就是反连接。它和内连接是一对补集：内连接给“配上的”，反连接给“没配上的”，两者加起来是左表全部行。写成 INNER JOIN 再加同样的 IS NULL 只会得到空结果，因为内连接已经把没配上的行删掉了。

本单元验收

做到下面 5 条，这一节就完成了。每条都给出你应该看到的真实结果。

  1. SELECT COUNT(*) FROM lesson18_dept; 是 5，SELECT COUNT(*) FROM lesson18_emp; 是 5。
  2. 员工左连接部门是 5 行（小李的部门列 NULL）；员工右连接部门是 6 行（财务部、人事部两行员工列 NULL）。
  3. 把 d.name = '研发部' 放 ON 仍是 5 行，放 WHERE 只剩 2 行——能解释为什么。
  4. 自连接 LEFT JOIN 是 5 行、JOIN 是 4 行；差额那一行是小周（没有上级）。
  5. UNION 4 行、UNION ALL 5 行；能说出 UNION 去的是整行重复，以及列数不等会报 1222。

做到这里

做到这里可以停。你现在能让配不上的行留下来（左/右外连接），能分清过滤条件放 ON 与 WHERE 的后果，能让一张表扮演两个角色，也能把两次查询竖直接起来并决定要不要去重。

下一节的方向是子查询：先算出一个结果，再拿它当条件用（P43 起）。本节只写到这个方向，具体内容在下一节，这里不提供空链接。

来源与边界

本节对应 BV1Kr4y1i7ru 平台 part 40「40. 基础-多表查询-外连接」、part 41「41. 基础-多表查询-自连接」、part 42「42. 基础-多表查询-联合查询union」（P40–P42，在 P01–P50 范围内），主题与同版讲义《MySQL-基础篇》5.4（p77–78）、5.5.1（p78–79）、5.5.2（p79–80）对应；讲义为公开镜像，仅作主题与字段语义核对，本节未复制讲义正文。

无逐字稿，本节不声称听过讲解、不引用原话、不写时间戳。讲义这几节沿用 emp / dept；本节换成自己建的两张表——lesson18_dept 5 行、lesson18_emp 5 行——列名与语义对齐（id / name / age / dept_id / salary / manager_id，讲义 emp.sal 是 DECIMAL(7,2)，本节用 INT 表示元），行数据、部门名全部原创。与第 17 节的 lesson17_* 是两套独立的表，本节不读取也不依赖它们。

本节每段 text 输出都在本机隔离的 MySQL 8.0.46 实例上逐条实跑（与学习服务器同版本号，不是同一台机器）；0.00 sec 是测试机耗时，你屏幕上可能是 0.01 sec，行列内容一致。页面不含 DROP / TRUNCATE / 整库重置语句；重跑靠 SHOW TABLES LIKE 'lesson18_%'; 和行数核对，不需要删表。