子查询：标量、列、行、表子查询

一句话理解

子查询就是 SELECT 里再套一个 SELECT：先算出内层的结果，再拿给外层当条件或当一列。

按这 4 步做

1. 先单独把内层跑一遍。看它返回“一个值、一列多行、一行多列、还是一张表”，这既是分类依据，也决定外层怎么接。
2. 再决定放哪里。WHERE 后面当条件最常见；SELECT 后面当一列（每行都带同一个值）；FROM 后面当临时表。 位置不同语义也不同：WHERE 里是过滤，SELECT 里是给每行加一列。
3. 最后检查外层能不能接得住。一个值可用 =、>；一列要用 IN；多列要用行比较；一张表必须放 FROM 并起别名。
4. 子查询不只能待在外层 SELECT 里，INSERT、UPDATE、DELETE 中同样可以嵌。

小例

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

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

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

内层先算出全公司平均 10000（8000+10000+12000 除以 3，NULL 不参与）。外层查研发部两人，每行都带上这个 10000。预期 2 行：小林 8000 / 10000、小周 10000 / 10000。放在 SELECT 列表里的子查询必须是单值，返回多行会报错。 注意这个平均是整张表的，不随外层部门变化；要“本部门平均”，得把外层的 dept_id 传进内层，也就是后面的相关子查询。

易错点

• 内层返回多行却用 = 接，报 ERROR 1242（Subquery returns more than 1 row）。
• SELECT 列表里的子查询可能每行都算一次，数据量大时别滥用。
• 子查询不是越深越好；两层能解决就别套四层，可读性也是正确性的一部分。

30 秒速记

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

材料与来源

一句话理解

标量子查询返回一个值（一行一列），所以能直接放在 =、>、< 后面当普通数字用。

按这 4 步做

1. 先确认内层只可能返回一行一列。按主键查、或用 AVG/MAX 这类聚合，天然只有一行；按普通字段查就可能翻车。
2. 把内层当成一个字面值来读。WHERE salary > (SELECT AVG(salary) ...) 就是“工资大于 10000”，理解上不需要特殊语法。
3. 跑之前先单独执行内层。看到 0 行或 2 行以上就要预警：0 行时内层得到 NULL，用 =、>、< 这类普通比较会得到 unknown，外层筛不出行；2 行以上直接报 1242。只有 IS NULL、IS NOT NULL 和 <=> 这类专门处理 NULL 的写法是例外——空集会被当成一个 NULL“值”来判，WHERE (SELECT ...) IS NULL 反而能查到行。
4. 写复杂条件时，先把内层结果抄成常量试跑外层，两次都对，组合起来基本不会错。

小例

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

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

-- 内层只有一个值：全公司平均工资
SELECT name AS 员工, salary AS 工资
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

-- 内层按 name 查，本数据里恰好一行；name 没有唯一约束，出现重名部门就会返回多行、外层报 1242
SELECT name AS 员工
FROM employee
WHERE dept_id = (SELECT id FROM department WHERE name = '测试部');

第一段内层 AVG = 10000，外层只剩小陈 12000，1 行。第二段内层查出测试部 id = 2，外层得到小陈，1 行。如果把“测试部”改成不存在的名字，内层 0 行 → 返回 NULL → 外层一行都没有；这不是语法错，是逻辑上空了（这里用的是 =，所以空集筛不出行；换成 IS NULL 判断就是例外）。 排查顺序也简单：先单独跑内层看行数，再把值写成常量跑外层，问题一定出在其中一步。要让内层稳定只返回一行，就按主键查，或用 AVG/MAX 这类聚合。

易错点

• 内层 0 行不是报错，而是得到 NULL，外层条件静默落空；排查时先单独跑内层。
• 内层返回 2 行以上报 ERROR 1242，常见于按 name 查而 name 没有唯一约束。
• AVG/SUM 跳过 NULL，但 COUNT(*) 不跳过；算平均工资时别把没工资的人当成 0。

30 秒速记

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

材料与来源

一句话理解

列子查询返回一列多行，等于给你一个集合：在集合里挑值用 IN，拿一个值和整个集合逐个比用 ANY / ALL。

按这 4 步做

1. 先确认内层只查一列。多查一列就变成表子查询，IN 接不住；一列多行意味着结果可能是 0 行、1 行或很多行。
2. IN 判断「在不在这个集合里」，最常用；NOT IN 是它的反面，但集合里有 NULL 时整体变成 unknown，结果常常是 0 行。
3. ANY（与 SOME 完全同义）是「至少有一个满足」：研发部工资是 8000、10000，salary > ANY（那个子查询）只要大于 8000 就成立，也就是大于集合里的最小值。
4. ALL 是「全部都满足」：同一份数据里 salary > ALL（那个子查询）要大于 10000，也就是大于集合里的最大值。写完用行数反推，别只看语法。

小例

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

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

-- IN：研发部和测试部的员工
SELECT name AS 员工, dept_id AS 部门号
FROM employee
WHERE dept_id IN (
  SELECT id FROM department WHERE name IN ('研发部','测试部')
);

-- ALL：比研发部所有人都高（= 大于最大值）
SELECT name AS 员工, salary AS 工资
FROM employee
WHERE salary > ALL (
  SELECT salary FROM employee WHERE dept_id = 1
);

-- ANY：比研发部“任意一人”工资高即可（= 大于最小值）
SELECT name AS 员工, salary AS 工资
FROM employee
WHERE salary > ANY (
  SELECT salary FROM employee WHERE dept_id = 1
)
ORDER BY id;

第一段内层返回 1、2 两个部门号，外层 3 行：小林、小周、小陈。第二段研发部工资是 8000、10000，> ALL 要大于 10000，只剩小陈 12000（1 行）。第三段 > ANY 只要大于 8000 就成立，小周 10000、小陈 12000 都入选（2 行）——同一份数据，ALL 与 ANY 的差别就是「全部」还是「任意一个」。记住方向：> ANY ≡ > MIN，> ALL ≡ > MAX；反过来 < ANY ≡ < MAX，< ALL ≡ < MIN。ALL 的好处是不用自己先想到 MAX，语义直接；ANY 的好处是「比其中任意一个高」这种需求能直说。

-- 集合 {3000, 6000, 9000}：5000 比最小的大、比最大的小
SELECT 5000 > ANY (SELECT 3000 UNION SELECT 6000 UNION SELECT 9000) AS 比任意一个大,
       5000 > ALL (SELECT 3000 UNION SELECT 6000 UNION SELECT 9000) AS 比全部都大;

SELECT (1 > ALL (SELECT salary FROM employee WHERE 1 = 0)) AS all_empty,
       (1 > ANY (SELECT salary FROM employee WHERE 1 = 0)) AS any_empty,
       ((5 > ALL (SELECT NULL)) IS NULL) AS all_null_unknown;

易错点

• NOT IN 的子查询结果含 NULL 时，整个条件永远不为真，结果 0 行；要么子查询加 WHERE dept_id IS NOT NULL，要么改用 NOT EXISTS。
• IN 后面的子查询只能返回一列；返回两列会报错，需要改成整行比较。
• 把 > ALL 读成「大于其中某一个」就全错了，那是 ANY 的语义；ALL 是「全部都要满足」。
• 以为 ANY 遇空集会全选：空集时 ANY 恒假（0 行），ALL 反而恒真。

30 秒速记

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

材料与来源

一句话理解

行子查询返回一行多列，用括号把几个字段凑成一行去比：(salary, manager_id) = (子查询)。

按这 3 步做

1. 先想清楚为什么要凑成一行。分成 salary = (...) AND manager_id = (...) 两个子查询，两个值可能来自不同的人，逻辑就错了；行比较保证同属一行。
2. 括号里的列顺序要一致。左边先 salary 后 manager_id，子查询也必须这个顺序；反了通常不报错，只是永远不相等。
3. 确认子查询只返回一行。行构造器配 = 时多行直接报 ERROR 1242；要多行匹配得换成 IN，那是下一页的表子查询。 行构造器不是万能元组比较，列数、类型都要严格对上。

小例

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

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

-- 与 3 号员工“工资 + 上级”都相同的人
SELECT id, name, salary, manager_id
FROM employee
WHERE (salary, manager_id) = (
  SELECT salary, manager_id FROM employee WHERE id = 3
);

内层按主键查到一行 (12000, 2)，外层逐行比较两个值是否同时相等，只有小陈自己，1 行。把条件换成 id = 2 会得到 (10000, NULL)：NULL 参与行比较结果未知，连小周自己都匹配不上——这是行子查询最隐蔽的坑。 行比较的语义是“同时相等”，不是“任一相等”；要任一相等得拆成 OR，别硬套行构造器。

易错点

• 行里任一列为 NULL，整行比较为 unknown，永远不相等；要匹配 NULL 用 <=> 或拆开写 IS NULL。
• 左右列数或顺序不一致，可能不报错但结果恒空，先逐列对齐。
• 多行结果不能用 =，报 ERROR 1242，改用 IN。
• (a,b) = (SELECT a FROM ...) 列数不匹配会报 ERROR 1241，先数括号里的列。

30 秒速记

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

材料与来源

一句话理解

表子查询返回多行多列。整行匹配用 (列1,列2) IN (子查询)；放进 FROM 当派生表时，MySQL 8 要求必须起别名。

按这 4 步做

1. 先看内层是不是多行多列。多行多列不能用 =，也不能按单列思维写，要把外层对应的几列用括号打包，整行去比。
2. 列的顺序和数量要对齐。(dept_id, salary) IN (SELECT dept_id, MAX(salary) ...) 左边两列，内层也必须两列、同顺序。
3. 放进 FROM 时它就是派生表。外层只能看到它 SELECT 出来的列，而且必须写 AS t；MySQL 8 不给别名报 ERROR 1248。
4. 能改写就改写，但先确认等价：表子查询换成 JOIN + GROUP BY 之前，先想清楚会不会因为一对多把行数放大、会不会丢掉 NULL 分组；改完用同一份数据对一遍行数和结果。想比快慢就看实际执行计划（EXPLAIN），别凭“JOIN 一定更快”下结论——数据量、索引和优化器都会变。

小例

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

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

-- 每行是“本部门最高工资”，整行匹配
SELECT id, name, dept_id, salary
FROM employee
WHERE (dept_id, salary) IN (
  SELECT dept_id, MAX(salary)
  FROM employee
  GROUP BY dept_id
);

-- 同一类结果放进 FROM，就是派生表（必须起别名）
SELECT t.dept_id, t.avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS t
WHERE t.avg_salary > 9000;

第一段内层给出两个组合：(1,10000)、(2,12000)，还有一组 (NULL,NULL) 因为小李没部门；IN 匹配不上 NULL，所以外层 2 行：小周、小陈。第二段派生表先算平均，研发部 9000、测试部 12000，外层筛大于 9000，只剩 2 号部门 1 行。 两段的共同点只是“内层都返回多行多列”：第一段拿每部门最高工资做整行匹配，第二段拿每部门平均工资当派生表再筛，聚合不同、行集也不同，不能互换；相同的是“多行多列可以当集合用 IN，也可以当表放 FROM”这个用法区别。先判断你要筛行，还是要再算一层。

易错点

• 多行多列的子查询用 = 接，直接报 1242 或列数错误。
• 派生表不给别名，MySQL 8 报 ERROR 1248 Every derived table must have its own alias。
• 内层 SELECT * 再让外层引用，列名重复且含义不清；派生表只留需要的列并起清晰别名。

30 秒速记

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

材料与来源