第九单元 · 子查询与综合练习 · 第 19 / 21 节

子查询（一）：先把内层单独跑一遍

先建本节自己的 lesson19_dept（4 行）和 lesson19_emp（7 行，1 人无部门、1 人无工资），用“许恬在哪个部门”和“公司平均工资”两个小例说清子查询只是嵌套 SELECT；再按返回形状走标量子查询（0 行得到 NULL、多行报 1242）和列子查询（IN、NOT IN 遇 NULL 归零、ANY / ALL 与空集），每一步都先单独跑内层。

3 个步骤 · 进度只存本机

约 24 分钟。子查询不是一条新语法，它只是把一条 SELECT 的结果交给另一条 SELECT 用。难点不在写，而在先看清内层返回什么形状：一个值、一列、一行，还是一张表。这一节先把“一个值”和“一列”用熟。

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;——它只切换当前连接用哪个库，不动数据。

第一步：子查询就是 SELECT 里再套一个 SELECT

这一步做完了

本节用自己的一套表，前缀 lesson19_。先看有没有：

MySQL 终端sql

SHOW TABLES LIKE 'lesson19_%';

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

Empty set 说明可以建。已经有表就跳过建表和插入，直接看本节末尾的终态核对，不要删表；重复执行建表会报“表已存在”，重复插数据会报主键冲突，这两种都不是本节要修的错误。

MySQL 终端sql

CREATE TABLE lesson19_dept (
  id   INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT uk_lesson19_dept_name UNIQUE (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 0 rows affected (0.03 sec)

MySQL 终端sql

INSERT INTO lesson19_dept (id, name) VALUES (1, '市场部'), (2, '技术部'), (3, '客服部'), (4, '法务部');

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL 终端sql

CREATE TABLE lesson19_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 lesson19_emp (id, name, age, dept_id, salary, manager_id) VALUES (1, '陈默', 24, 1, 7200, NULL), (2, '周岚', 29, 1, 9600, 1), (3, '许恬', 33, 2, 11800, 1), (4, '高远', 27, 2, 8600, 3), (5, '罗晴', 31, NULL, 7900, 3), (6, '沈舟', 26, 3, NULL, 2), (7, '韩露', 38, 3, 10400, 1);

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

七名员工，其中罗晴没有部门、沈舟没有工资。这两个空值后面都会用到。

子查询的第一步永远是：把内层单独拿出来跑。比如“许恬在哪个部门”：

MySQL 终端sql

SELECT dept_id FROM lesson19_emp WHERE id = 3;

输出示例（只读，不要粘贴执行）text不可复制

+---------+
| dept_id |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

只有一行一列，值 2。这就是一个标准量，可以直接放到外层当普通数字用：

MySQL 终端sql

SELECT name FROM lesson19_dept WHERE id = (SELECT dept_id FROM lesson19_emp WHERE id = 3);

输出示例（只读，不要粘贴执行）text不可复制

+-----------+
| name      |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

括号里的部分先算出一个 2，外层再拿 id = 2 去找部门，读起来和 WHERE id = 2 没有区别。

子查询也可以放在 SELECT 后面，这时它是“给每一行加一列”：

MySQL 终端sql

SELECT name AS 员工, salary AS 工资, (SELECT AVG(salary) FROM lesson19_emp) AS 公司平均 FROM lesson19_emp WHERE dept_id = 1 ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+--------+--------+--------------+
| 员工   | 工资   | 公司平均     |
+--------+--------+--------------+
| 陈默   |   7200 |    9250.0000 |
| 周岚   |   9600 |    9250.0000 |
+--------+--------+--------------+
2 rows in set (0.00 sec)

内层 AVG(salary) 是 9250.0000（7200 + 9600 + 11800 + 8600 + 7900 + 10400，再除以 6；沈舟的 NULL 不参与平均）。外层查市场部两人，每行都带同一个公司平均。注意这个平均值是整张表的，不随部门变化；要“本部门平均”，得把外层的部门号传进内层，那是 lesson21 的相关子查询。

按返回形状，子查询分四类，这一节和下一节正好走完：

形状	内层返回	外层怎么接	本课位置
标量子查询	一行一列（一个值）	`=` `>` `<` 等普通比较	本节第二步
列子查询	一列多行（一个集合）	`IN` / `ANY` / `ALL`	本节第三步
行子查询	一行多列	括号打包整行比较	第 20 节
表子查询	多行多列（一张表）	整行 `IN`，或放 `FROM` 当派生表	第 20 节

按位置分，子查询可以出现在 WHERE 后面（当条件）、SELECT 后面（当一列）、FROM 后面（当一张临时表）。外面那条语句也不一定是查询，INSERT、UPDATE、DELETE 里同样可以嵌；本课只练 SELECT 里的三种位置。

第二步：标量子查询——一个值，也怕“不止一个”

这一步做完了

上一步已经算过公司平均。现在直接拿它当门槛：

MySQL 终端sql

SELECT name AS 员工, salary AS 工资 FROM lesson19_emp WHERE salary > (SELECT AVG(salary) FROM lesson19_emp) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+--------+--------+
| 员工   | 工资   |
+--------+--------+
| 周岚   |   9600 |
| 许恬   |  11800 |
| 韩露   |  10400 |
+--------+--------+
3 rows in set (0.00 sec)

内层 9250.0000，外层的 > 就是“工资大于 9250”，三人入选。写法上不需要特殊照顾，因为内层只可能返回一行一列。

但“只可能”三个字要自己保证。按主键查、或者用 AVG MAX 这类聚合，天然只有一行；按普通字段查就可能翻车：

MySQL 终端sql

SELECT name AS 员工 FROM lesson19_emp WHERE dept_id = (SELECT dept_id FROM lesson19_emp WHERE salary > 9000);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1242 (21000): Subquery returns more than 1 row

内层有 3 行（9600、11800、10400 三个人的部门号），外层却用 = 去接，数据库不知道取哪一个，直接报 1242。1242 不是“没查到”，是“查到太多”；先单独跑内层，看到行数大于 1 就要改写法，通常改成 IN（下一步）。

另一头是 0 行。查一个不存在的部门：

MySQL 终端sql

SELECT (SELECT id FROM lesson19_dept WHERE name = '行政部') AS 查不到的结果;

输出示例（只读，不要粘贴执行）text不可复制

+--------------------+
| 查不到的结果       |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

内层 0 行，整个子查询当作一个 NULL。它不报错，外层用普通比较时也筛不出任何行：

MySQL 终端sql

SELECT name AS 员工 FROM lesson19_emp WHERE dept_id = (SELECT id FROM lesson19_dept WHERE name = '行政部');

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

这不是语法错，是逻辑上空了。要判断“内层到底有没有查到”，得用专门处理 NULL 的写法，比如 IS NULL：

MySQL 终端sql

SELECT name AS 员工 FROM lesson19_emp WHERE (SELECT id FROM lesson19_dept WHERE name = '行政部') IS NULL ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+--------+
| 员工   |
+--------+
| 陈默   |
| 周岚   |
| 许恬   |
| 高远   |
| 罗晴   |
| 沈舟   |
| 韩露   |
+--------+
7 rows in set (0.00 sec)

这条能返回全部七行，因为子查询对每一行都是 NULL，IS NULL 为真。它说明“空集被当成一个 NULL 值”，而不是“没有这个值”。但 = NULL 永远不为真，所以排查时不要写 = NULL。

还有一个小坑：AVG SUM 会跳过 NULL，COUNT(*) 不会。本节平均工资的除数 6 而不是 7，就是因为沈舟的工资是 NULL；如果把他当 0 算，平均会变成 7928.5714，后面的门槛就全错了。

第三步：列子查询——一列多行就是一个集合

这一步做完了

内层返回一列、但可能有很多行，这时外层不能用 =，要用集合运算符。先看内层：

MySQL 终端sql

SELECT id FROM lesson19_dept WHERE name IN ('市场部', '技术部') ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

两行，1 和 2。把它放进 IN：

MySQL 终端sql

SELECT name AS 员工, dept_id AS 部门号 FROM lesson19_emp WHERE dept_id IN (SELECT id FROM lesson19_dept WHERE name IN ('市场部', '技术部')) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+--------+-----------+
| 员工   | 部门号    |
+--------+-----------+
| 陈默   |         1 |
| 周岚   |         1 |
| 许恬   |         2 |
| 高远   |         2 |
+--------+-----------+
4 rows in set (0.00 sec)

四行：市场部两人、技术部两人。IN 读作“在不在这个集合里”，集合是 0 行、1 行还是多行都不影响写法。

ANY 和 ALL 也是接集合的，但意思差得很远。市场部（部门号 1）的工资是 7200 和 9600：

MySQL 终端sql

SELECT name AS 员工, salary AS 工资 FROM lesson19_emp WHERE salary > ALL (SELECT salary FROM lesson19_emp WHERE dept_id = 1) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+--------+--------+
| 员工   | 工资   |
+--------+--------+
| 许恬   |  11800 |
| 韩露   |  10400 |
+--------+--------+
2 rows in set (0.00 sec)

> ALL 要求比集合里每一个都大，也就是大于最大值 9600，只剩许恬 11800 和韩露 10400 两人。

MySQL 终端sql

SELECT name AS 员工, salary AS 工资 FROM lesson19_emp WHERE salary > ANY (SELECT salary FROM lesson19_emp WHERE dept_id = 1) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+--------+--------+
| 员工   | 工资   |
+--------+--------+
| 周岚   |   9600 |
| 许恬   |  11800 |
| 高远   |   8600 |
| 罗晴   |   7900 |
| 韩露   |  10400 |
+--------+--------+
5 rows in set (0.00 sec)

> ANY 只要求“任意一个满足”，也就是大于最小值 7200，周岚、许恬、高远、罗晴、韩露五人都入选。同一份数据，ALL 和 ANY 的门槛一个在最大值、一个在最小值，方向别记反：

> ANY 等价于 > MIN；> ALL 等价于 > MAX；
反过来，< ANY 等价于 < MAX；< ALL 等价于 < MIN。

ANY 和 SOME 完全同义，写哪个都行，本课统一写 ANY。

集合里可能有 NULL，这是列子查询最隐蔽的一处。查“没有员工的部门”：

MySQL 终端sql

SELECT id, name FROM lesson19_dept WHERE id NOT IN (SELECT dept_id FROM lesson19_emp) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

一行也没有。因为内层包含罗晴的 NULL，NOT IN (1, 2, 3, NULL) 对每个部门都变成 unknown，整个条件不为真。把 NULL 排除掉就正常了：

MySQL 终端sql

SELECT id, name FROM lesson19_dept WHERE id NOT IN (SELECT dept_id FROM lesson19_emp WHERE dept_id IS NOT NULL) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+-----------+
| id | name      |
+----+-----------+
|  4 | 法务部    |
+----+-----------+
1 row in set (0.00 sec)

法务部（4 号）才是唯一没有员工的部门。

最后看一眼空集上的 ANY / ALL。法务部没有任何员工：

MySQL 终端sql

SELECT (SELECT COUNT(*) FROM lesson19_emp WHERE salary > ANY (SELECT salary FROM lesson19_emp WHERE dept_id = 4)) AS 比任意一个大, (SELECT COUNT(*) FROM lesson19_emp WHERE salary > ALL (SELECT salary FROM lesson19_emp WHERE dept_id = 4)) AS 比全部都大;

输出示例（只读，不要粘贴执行）text不可复制

+--------------------+-----------------+
| 比任意一个大       | 比全部都大      |
+--------------------+-----------------+
|                  0 |               7 |
+--------------------+-----------------+
1 row in set (0.00 sec)

ANY 要有“至少一个”比较对象，空集里没有，所以 0 人；ALL 要求“每一个都满足”，没有对象时条件恒真，七行全算进来——连工资为 NULL 的沈舟也在内。这个结果反直觉，但它是 ALL 在空集上的定义。记住“空集让 ALL 恒真、让 ANY 恒假”，排查时才不会把 0 行当成语法错。

常见报错怎么办

报错	什么意思	怎么办
`1242 Subquery returns more than 1 row`	内层返回多行，外层却用 `=` 接	先单独跑内层数行数；多行改 `IN`，或加聚合/主键条件让它只剩一行
`1062 Duplicate entry`	种子数据重复插入	表已存在就跳过建表和插入，按行数核对，不要删表

说明

MySQL 终端sql

INSERT INTO lesson19_dept (id, name) VALUES (1, '市场部');

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1062 (23000): Duplicate entry '1' for key 'lesson19_dept.PRIMARY'

表已经存在时，重跑建表和插入都会失败：建表会报“表已存在”，插数据就是上面的 1062。这不是数据坏了，是“已经做过”。正确做法是跳过建表/插入，直接用行数核对：

MySQL 终端sql

SELECT (SELECT COUNT(*) FROM lesson19_dept) AS 部门数, (SELECT COUNT(*) FROM lesson19_emp) AS 员工数;

输出示例（只读，不要粘贴执行）text不可复制

+-----------+-----------+
| 部门数    | 员工数    |
+-----------+-----------+
|         4 |         7 |
+-----------+-----------+
1 row in set (0.00 sec)

四个部门、七名员工，就是本节要的初始状态。不要为了“重来一遍”去删表或清空表。

练习

1. 查出工资高于全公司平均的员工姓名和工资。

说明

先用一条 SELECT AVG(salary) 单独看平均值，再把这条子查询原样放进 WHERE。注意沈舟没有工资。

说明

MySQL 终端sql

SELECT name AS 员工, salary AS 工资 FROM lesson19_emp WHERE salary > (SELECT AVG(salary) FROM lesson19_emp) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+--------+--------+
| 员工   | 工资   |
+--------+--------+
| 周岚   |   9600 |
| 许恬   |  11800 |
| 韩露   |  10400 |
+--------+--------+
3 rows in set (0.00 sec)

说明

把 > 写成 >= 不会多出人（没有人正好等于 9250）；真正的坑是把 AVG(salary) 写成 AVG(IFNULL(salary,0))，平均值被拉低，入选的人就变多了。NULL 不参与平均是这里的正确行为。

2. 查出技术部和客服部的所有员工。

说明

内层只查部门号、可能返回两行，所以外层要用集合运算符，不能用 =。

说明

MySQL 终端sql

SELECT name AS 员工, dept_id AS 部门号 FROM lesson19_emp WHERE dept_id IN (SELECT id FROM lesson19_dept WHERE name IN ('技术部', '客服部')) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+--------+-----------+
| 员工   | 部门号    |
+--------+-----------+
| 许恬   |         2 |
| 高远   |         2 |
| 沈舟   |         3 |
| 韩露   |         3 |
+--------+-----------+
4 rows in set (0.00 sec)

说明

写成 dept_id = (SELECT ...) 会报 1242，因为内层返回两个部门号。看到 1242 先数内层行数，不要怀疑表名。

3. 查出没有任何员工的部门。

说明

先单独跑 SELECT dept_id FROM lesson19_emp，看看里面有没有 NULL。NOT IN 遇到 NULL 会整体失效。

说明

MySQL 终端sql

SELECT id, name FROM lesson19_dept WHERE id NOT IN (SELECT dept_id FROM lesson19_emp) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

内层有罗晴的 NULL，所以上面这条一行也查不到。把 NULL 先排除：

MySQL 终端sql

SELECT id, name FROM lesson19_dept WHERE id NOT IN (SELECT dept_id FROM lesson19_emp WHERE dept_id IS NOT NULL) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+-----------+
| id | name      |
+----+-----------+
|  4 | 法务部    |
+----+-----------+
1 row in set (0.00 sec)

说明

以为“没查到”是数据问题。其实是 NOT IN (..., NULL) 的比较结果变成 unknown，整个条件不为真。要么内层加 IS NOT NULL，要么改用 NOT EXISTS。不要用 = NULL 去试。

4. 法务部一个人也没有。把“工资比法务部任意一人高”和“工资比法务部所有人都高”的人数各查一遍。

说明

两条都用同一个空集做内层，只换 ANY / ALL。空集时两者结果相反。

说明

MySQL 终端sql

SELECT (SELECT COUNT(*) FROM lesson19_emp WHERE salary > ANY (SELECT salary FROM lesson19_emp WHERE dept_id = 4)) AS 比任意一个大, (SELECT COUNT(*) FROM lesson19_emp WHERE salary > ALL (SELECT salary FROM lesson19_emp WHERE dept_id = 4)) AS 比全部都大;

输出示例（只读，不要粘贴执行）text不可复制

+--------------------+-----------------+
| 比任意一个大       | 比全部都大      |
+--------------------+-----------------+
|                  0 |               7 |
+--------------------+-----------------+
1 row in set (0.00 sec)

ANY 要有“至少一个”比较对象，空集里没有，所以是 0 人；ALL 要求“每一个都满足”，没有对象时条件恒真，七行全算进来——连工资为 NULL 的沈舟也在内。这个结果反直觉，但它是 ALL 在空集上的定义，记住“空集让 ALL 恒真”即可。

说明

以为两个都该是 0。另一个错是拿这条去证明“法务部工资很高”，空集不能用来比大小；它只能说明“没有可比对象”。

本单元验收

能拿到一条子查询，先单独跑内层，并说出它返回的是标量还是列。
能解释标量子查询 0 行（得到 NULL）和 2 行以上（1242）分别会发生什么。
能写出 IN / ANY / ALL 三条查询，并说清 > ALL 等价于“大于最大值”、> ANY 等价于“大于最小值”。
能解释 NOT IN 的子查询里出现 NULL 为什么会让结果变成 0 行。

做到这里

收工前核对本节留下的数据，五个数字对上就说明你跑的是同一份：

MySQL 终端sql

SELECT (SELECT COUNT(*) FROM lesson19_dept) AS 部门数, (SELECT COUNT(*) FROM lesson19_emp) AS 员工数, (SELECT COUNT(*) FROM lesson19_emp WHERE dept_id IS NULL) AS 无部门, (SELECT COUNT(*) FROM lesson19_emp WHERE salary IS NULL) AS 无工资, (SELECT AVG(salary) FROM lesson19_emp) AS 平均工资;

输出示例（只读，不要粘贴执行）text不可复制

+-----------+-----------+-----------+-----------+--------------+
| 部门数    | 员工数    | 无部门    | 无工资    | 平均工资     |
+-----------+-----------+-----------+-----------+--------------+
|         4 |         7 |         1 |         1 |    9250.0000 |
+-----------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

本节停在这里：你会先跑内层、再按形状接外层，标量和列这两类子查询已经能用。下一节处理剩下两类——一行多列用括号整行比，多行多列当集合或当临时表。仍然只用自己的 lesson20_* 表，不读本节的数据。

来源与边界

本节对应 BV1Kr4y1i7ru 平台 part 43「43. 基础-多表查询-子查询介绍」、part 44「44. 基础-多表查询-标量子查询」、part 45「45. 基础-多表查询-列子查询」，主题与同版讲义《MySQL-基础篇》5.6.1–5.6.3（p80–83）对应；讲义为公开镜像，仅作事实核对。无逐字稿，未声称听过讲解；来源只写平台 part 号与讲义页码。本节表名、数据、查询与练习均为本教程原创，未复制讲义正文；讲义示例用部门名和员工姓名，本节改用另一套部门与姓名，数字也全部另取。课程停在 P50，本节不涉及事务、索引、引擎或执行计划。