MySQL 复合查询：多表 / 自连接 / 子查询 / UNION，一篇讲透

0. 开篇：单表查询的天花板
1. 实验环境：三张经典表
2. 基本查询回顾（8 道题，每题一个细节）
3. 多表查询：从「笛卡尔积」说起
4. 自连接：把一张表当成两张用
5. 子查询：SQL 里的「套娃」
6. 合并查询：UNION 与 UNION ALL
7. 补全：多表查询的执行顺序与性能思维
8. 实战 OJ
9. 报错速查
10. 面试高频 12 问
11. 总结：一张图 + 一句口诀
附：一键可执行完整脚本

0. 开篇：单表查询的天花板

先看一个需求，来自课件的第一个多表案例：

显示雇员名、雇员工资，以及所在部门的名字。

你会发现单表查询做不了：

雇员名、工资 → 在 EMP 表
部门名 → 在 DEPT 表

这不是语法问题，是数据被「拆」开了。

为什么要把数据拆开？因为数据库设计的第三范式：

如果每条员工记录里都存一份部门名，那「ACCOUNTING」这个词要重复存 3 遍（10 号部门有 3 个人）
部门改名时，3 条记录都要改 → 更新异常
如果某个部门还没人，部门名就无处可存 → 插入异常

所以规范的做法是：部门信息存一份在 DEPT，员工表只存 deptno 这个「引用」。

这就是「关系型数据库」里「关系」二字的含义——数据之间存在关联，查询时要把关联拼回来。

而拼回来的手段只有三种：

手段关键词一句话

多表查询	from a, b where … / join	两张表并列，按条件配对
自连接	同一张表起两个别名	表跟自己配对
子查询	select 里套 select	先算一个中间结果，再拿它去查

加上最后收尾的 UNION（把两个结果集拼起来），就是本篇的全部内容。

而它们背后有一个共同的影子，你必须先认识它——笛卡尔积。

1. 实验环境：三张经典表

课件用的是 Oracle 9i 的经典测试表 EMP / DEPT / SALGRADE。这套表从 1980 年代用到现在，几乎所有数据库教材都在用它，值得你亲手建一遍。

create database if not exists multi_demo default character set utf8mb4;
use multi_demo;

— ============ 部门表 ============
create table dept (
deptno int primary key,
dname varchar(14),
loc varchar(13)
);
insert into dept values
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

— ============ 员工表 ============
create table emp (
empno int primary key,
ename varchar(10),
job varchar(9),
mgr int,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int
);
insert into emp values
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);

— ============ 工资等级表 ============
create table salgrade (
grade int,
losal int,
hisal int
);
insert into salgrade values
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);

先记住三张表的「规模」，后面会用到：

表行数说明

emp	14	14 个员工
dept	4	4 个部门（40 号 OPERATIONS 部门一个员工都没有！）
salgrade	5	5 个工资档

⚠️ 请特别注意：dept 有 4 个部门，但 emp 里只有 10/20/30 三个部门的员工——40 号部门是「空部门」。

这个 40 号部门是整篇课件埋下的一个伏笔，也是本篇结尾（和下一篇）最重要的一个坑。先记住它。

🔗 前后照应：emp.deptno 就是第 05 篇讲的外键关系（虽然这里没建物理外键）。deptno 上没有外键约束，所以理论上你可以插入 deptno = 99 的员工——这正是第 05 篇 9.6 节说的「逻辑外键」场景。

2. 基本查询回顾（8 道题，每题一个细节）

课件先用 8 道单表题热身。别跳过——这 8 道题里藏着后面多表查询要用的全部零件。

2.1 工资高于 500 或岗位为 MANAGER，且姓名首字母为 J

select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

结果：

+——-+——-+———–+——+————+———+——+——–+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+——-+——-+———–+——+————+———+——+——–+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+——-+——-+———–+——+————+———+——+——–+

⚠️ 这道题唯一的价值就是那个括号。

AND 的优先级高于 OR。如果去掉括号：

— ❌ 错误：等价于 sal > 500 OR (job='MANAGER' AND ename like 'J%')
select * from emp where sal > 500 or job = 'MANAGER' and ename like 'J%';

因为所有人工资都 > 500，所以这个查询会返回全表 14 行。

💡 凡是 AND 和 OR 混用，一律加括号。这不花钱，只救命。 🔗 这和第 06 篇 3.4.8 节那道「孙某同学，否则要求总成绩 > 200……」是同一个考点。

2.2 按部门号升序、工资降序排序

select * from emp order by deptno, sal desc;

结果：

+——-+——–+———–+——+————+———+——+——–+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+——-+——–+———–+——+————+———+——+——–+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 |1400.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+——-+——–+———–+——+————+———+——+——–+

三个观察：

① 第一排序键相同，才轮到第二排序键 —— 20 号部门里 SCOTT 和 FORD 都是 3000，此时它们的相对顺序由 MySQL 决定（不保证稳定）。

🔗 前后照应：第 06 篇 3.5.2 讲过「排序不稳定会导致分页重复/丢数据」。这里就是活例子：加一个 empno 作为 tiebreaker 才安全。

order by deptno, sal desc, empno

② deptno 没写 ASC，默认就是升序。别被「不写就不排」误导。

③ 40 号部门根本不在结果里 —— 因为它没有员工。这个细节，是全文最重要的伏笔。如果需求是「显示所有部门，包括没有员工的」，order by deptno 是做不到的——你需要外连接（下一篇）。

2.3 用年薪降序排序

select ename, sal * 12 + ifnull(comm, 0) as '年薪' from emp order by 年薪 desc;

这一行 SQL 里有三个知识点，必须全部吃透：

① ifnull(comm, 0) 为什么必须写？

因为 comm（奖金）有很多行是 NULL。而：

select 2000 * 12 + null; — NULL

sal * 12 + comm 只要 comm 是 NULL，整个年薪就变成 NULL。

🔗 前后照应：这是 NULL 的第 N 次出场。第 05 篇说「1 + null = null」，第 06 篇说「chinese + math + english 会变 NULL」，第 07 篇说「concat 遇 NULL 全变 NULL」，这里是第四次——同一个规律，换了个战场。

凡是要做「跨列算术」，先问一句：这一列可能是 NULL 吗？

② as '年薪' 用了中文别名，然后 order by 年薪直接引用了它。

🔗 前后照应：第 06 篇 3.4.6 讲过「WHERE 里不能用别名，但 ORDER BY 能用」，原因就是执行顺序。这里正是那个结论的实战：ORDER BY 在 SELECT 之后执行，所以别名可见。

③ 结果长这样：

+——–+———-+
| ename | 年薪 |
+——–+———-+
| KING | 60000.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| JONES | 35700.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| ALLEN | 19500.00 |
| MARTIN | 16400.00 |
| TURNER | 18000.00 |
| WARD | 15500.00 |
| ADAMS | 13200.00 |
| MILLER | 15600.00 |
| JAMES | 11400.00 |
| SMITH | 9600.00 |
+——–+———-+

注意 comm 为 NULL 的人（如 KING）年薪是 5000 × 12 = 60000，因为 ifnull(NULL, 0) = 0。如果不写 ifnull，KING 的年薪会是 NULL 并排在最后（降序时 NULL 最小）。

2.4 工资最高的员工的名字和岗位

select ename, job from emp where sal = (select max(sal) from emp);

这是本篇的第一个子查询**，非常典型的「单行子查询」。**

为什么不能写成这样？

— ❌ 报错 ERROR 1111: Invalid use of group function
select ename, job from emp where sal = max(sal);

因为 WHERE 是「逐行过滤」，而 max(sal) 是「整表聚合」——两者的作用范围对不上。

🔗 前后照应：第 06 篇 7.5 节讲过同样的报错——聚合函数不能出现在 WHERE 里。当时给的解法是「用 HAVING」，但**HAVING 只能配合 GROUP BY 用**。像这里根本没有分组需求，怎么办？

答案就是子查询：先用子查询跑出一个「标量值」（单行单列），再拿这个值去和每一行比较。

这就是子查询存在的第一意义：把「聚合结果」变成「一个可以参与比较的值」。

2.5 工资高于平均工资的员工信息

select ename, sal from emp where sal > (select avg(sal) from emp);

结果：

+——-+———+
| ename | sal |
+——-+———+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+——-+———+

平均工资是 2073.21…，上面 6 个人高于它。

这道题要注意一个「直觉陷阱」：

很多人会说「用 join 更高效」。不。这里两表都是 emp，用 join 反而绕。子查询是最直接的表达。

而这道题真正的高阶版本在后面（5.4 节）：不是「高于全公司平均」，而是**「高于自己部门的平均」**——那时候子查询就必须变成「相关子查询」或「派生表」了。

2.6 每个部门的平均工资和最高工资

select deptno, format(avg(sal), 2), max(sal) from emp group by deptno;

结果：

+——–+——————–+———+
| deptno | format(avg(sal),2) | max(sal)|
+——–+——————–+———+
| 10 | 2916.67 | 5000.00 |
| 20 | 2175.00 | 3000.00 |
| 30 | 1566.67 | 2850.00 |
+——–+——————–+———+

format(avg(sal), 2) 的作用：把平均值保留两位小数。

🔗 前后照应：第 07 篇 4.2 节讲过 round / format / truncate 三兄弟。这里用的是 format——它返回字符串，但用于「展示」场景正好合适。如果要做后续计算，应该用 round。

再注意一遍：只有 3 个部门出现在结果里。40 号部门又消失了。

2.7 平均工资低于 2000 的部门号和平均工资

select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;

结果：

+——–+———-+
| deptno | avg_sal |
+——–+———-+
| 30 | 1566.67 |
+——–+———-+

HAVING + 别名 —— 这又是第 06 篇 8.6 节的实战验证。

— ❌ 这样写会报错 1111
select deptno, avg(sal) from emp where avg(sal) < 2000 group by deptno;

— ✅ 必须用 having
select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;

2.8 每种岗位的雇员总数、平均工资

select job, count(*), format(avg(sal), 2) from emp group by job;

结果：

+———–+———-+——————–+
| job | count(*) | format(avg(sal),2) |
+———–+———-+——————–+
| CLERK | 4 | 1037.50 |
| SALESMAN | 4 | 1400.00 |
| MANAGER | 3 | 2758.33 |
| ANALYST | 2 | 3000.00 |
| PRESIDENT | 1 | 5000.00 |
+———–+———-+——————–+

这里 count(*) 用的是「行数」统计。如果用 count(comm)，SALESMAN 只会算出 4 个里有几个有奖金——

select job, count(*), count(comm), format(avg(sal), 2) from emp group by job;
— SALESMAN: count(*)=4, count(comm)=4（4 个都有奖金，只是 TURNER 是 0.00 不是 NULL）
— CLERK: count(*)=4, count(comm)=0（全都是 NULL）

🔗 前后照应：第 06 篇 7.2 节那张「COUNT(*) / COUNT(1) / COUNT(列)」的对照表，在这里得到验证——count(列) 不统计 NULL。

顺带注意：TURNER 的 comm 是 0.00 而不是 NULL。「0」和「NULL」是两回事——0 是有奖金但没发，NULL 是压根没奖金概念。这个区别在做「平均奖金」时影响巨大。

3. 多表查询：从「笛卡尔积」说起

3.1 先看一个「恐怖」的现象

select * from emp, dept;

结果有多少行？

14 × 4 = 56 行

是的，56 行。emp 的每一行，都和 dept 的每一行配了一次对。

这就是笛卡尔积（Cartesian Product）：

emp（14 行） dept（4 行）结果（56 行）
├ SMITH ├ 10 ACCOUNTING → SMITH + 10 ACCOUNTING
│ ├ 20 RESEARCH → SMITH + 20 RESEARCH
│ ├ 30 SALES → SMITH + 30 SALES
│ └ 40 OPERATIONS → SMITH + 40 OPERATIONS
├ ALLEN ├ 10 … → ALLEN + 10 ACCOUNTING
…

从数学上看：A × B = 所有可能的「有序对」。

从数据库角度看：这是一场灾难。

表 A 行数表 B 行数笛卡尔积行数

14	4	56
1,000	1,000	1,000,000
100,000	1,000	100,000,000（一亿！）

所以「不加连接条件的多表查询」在生产环境基本等于「自杀」。

3.2 加连接条件：把「对的配对」筛出来

select EMP.ename, EMP.sal, DEPT.dname
from EMP, DEPT
where EMP.deptno = DEPT.deptno;

结果：

14 行。因为 where EMP.deptno = DEPT.deptno 从 56 行里筛掉了「部门号对不上」的 42 行。

这条 SQL 的执行过程（非常重要）：

① 生成笛卡尔积：emp × dept → 56 行
② 用 where 过滤：只保留 deptno 相等的 → 14 行
③ 投影（select）：只取 ename, sal, dname 三列

课件那句注释说得非常精准：

其实我们只要 emp 表中的 deptno = dept 表中的 deptno 字段的记录

这句话翻译成 SQL 就是 where EMP.deptno = DEPT.deptno。而「笛卡尔积 + 筛选」这个模型，正是理解所有 JOIN 的钥匙。

💡 一个重要的认知升级：在关系代数里，from a, b where 条件和 a inner join b on 条件是完全等价的两件事。前者叫「隐式连接」（implicit join），后者叫「显式连接」（explicit join）。 MySQL 优化器会把它们优化成同一个执行计划。所以性能上没有区别，区别只在可读性。

下一篇（内外连接）会详细讲这个等价关系，以及它在外连接下不成立的那个大坑。

3.3 案例二：显示 10 号部门的部门名、员工名和工资

select ename, sal, dname
from EMP, DEPT
where EMP.deptno = DEPT.deptno and DEPT.deptno = 10;

结果：

+——–+———+————+
| ename | sal | dname |
+——–+———+————+
| CLARK | 2450.00 | ACCOUNTING |
| KING | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+——–+———+————+

10 号部门（ACCOUNTING）有 3 个人，正确。

这里有个小细节：DEPT.deptno = 10 和 EMP.deptno = 10 效果一样吗？

一样，因为连接条件已经保证了两张表的 deptno 相等。但语义上，用 DEPT.deptno = 10 更准确——你想筛的是「部门」，而不是「员工的部门号字段」。

3.4 案例三：显示各个员工的姓名、工资及工资级别

select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

结果：

+——–+———+——-+
| ename | sal | grade |
+——–+———+——-+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+——–+———+——-+

这道题的精妙之处：连接条件不是 =，而是 between … and …。

where EMP.sal between losal and hisal

「连接条件」不一定非要用等号！只要是一个「能判断两行是否匹配」的布尔表达式，都可以当连接条件。这在「区间匹配」「时间重叠」这类场景里极其常见：

— 判断两个时间段是否重叠（会议排期、库存占用）
where a.start_time < b.end_time and a.end_time > b.start_time

这道题还有一个陷阱：如果 sal 恰好落在两个区间的边界上（比如 1200 同时在 grade 1 的 [700,1200] 和 grade 2 的 [1201,1400] 之外），不会有重复。但如果区间有重叠，一个员工会匹配出多个等级，结果行数会多于 14 行。

设计区间表时，区间必须「首尾相接、互不重叠」，否则 join 会出现重复行。这是数据质量问题，SQL 救不了你。

4. 自连接：把一张表当成两张用

4.1 需求

显示员工 FORD 的上级领导的编号和姓名。（mgr 是员工领导的编号 → 对应 empno）

问题来了：FORD 的上级信息也在这张 emp 表里**，只是换了一行。**

要拿到「JONES」这个名字，得用 emp 表去查 emp 表。

4.2 解法一：子查询（两步走）

select empno, ename from emp where emp.empno = (select mgr from emp where ename = 'FORD');

结果：

+——-+——-+
| empno | ename |
+——-+——-+
| 7566 | JONES |
+——-+——-+

执行过程：

① 内层：(select mgr from emp where ename='FORD') → 7566
② 外层：select … from emp where empno = 7566 → JONES

先用子查询「查出线索」，再用这个线索「反查」。这是子查询最朴素也最常用的用法。

4.3 解法二：自连接（一步到位）

— 使用到表的别名
— from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别
select leader.empno, leader.ename
from emp leader, emp worker
where leader.empno = worker.mgr and worker.ename = 'FORD';

结果完全一样：

+——-+——-+
| empno | ename |
+——-+——-+
| 7566 | JONES |
+——-+——-+

课件那句注释极其重要，我要重点讲：

from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别

这句话揭示了自连接的本质：

① 把 emp 复制成「两份视角」：
leader（我是领导） worker（我是下属）
② 做笛卡尔积：14 × 14 = 196 行
③ 用条件筛选：leader.empno = worker.mgr → 只剩「真正的上下级关系」
④ 再加 worker.ename='FORD' → 只剩 FORD 那一组

关键理解：leader 和 worker 是同一张表，只是「扮演不同角色」。

别名扮演什么从哪里取数

leader	被查的那个人（领导）	从 leader.* 取 empno、ename
worker	提供线索的人（下属）	从 worker.* 取 mgr、ename

所以 where leader.empno = worker.mgr 的含义是：

「领导」的工号 = 「下属」记录里那个「我的领导是谁」字段

4.4 为什么自连接必须起别名？

— ❌ 报错
select empno, ename from emp, emp where emp.empno = emp.mgr;
— ERROR 1066 (42000): Not unique table/alias: 'emp'

因为 MySQL 需要一个「唯一名字」来区分这两份 emp。同名的两张表，emp.empno 到底指哪一份？它无法判断。

起别名后：

— ✅
select leader.empno, leader.ename from emp leader, emp worker
where leader.empno = worker.mgr and worker.ename = 'FORD';

leader 和 worker 是两个唯一的别名，无歧义。

💡 这也是「给表起别名」最重要的理由之一（另一个理由是「表名太长，写起来痛苦」）。在多表查询里，只要出现同名列（empno、deptno、ename 两张表都有），就必须用表名.列名消歧，否则报 ERROR 1052: Column 'xxx' in field list is ambiguous。

4.5 什么时候「必须」用自连接？

自连接不是炫技，有些需求只能用自连接（或相关子查询）解决。典型的三类：

① 同一张表内的「层级关系」

— 员工 → 领导 → 领导的领导（多级）
select e1.ename as 员工, e2.ename as 直属领导, e3.ename as 上级领导
from emp e1
left join emp e2 on e1.mgr = e2.empno
left join emp e3 on e2.mgr = e3.empno;

② 同一张表内的「两行比较」

— 找出「比同部门其他人工资高」的员工
select e1.ename, e1.sal
from emp e1, emp e2
where e1.deptno = e2.deptno and e1.sal > e2.sal;
— 注意：这个写法会返回重复行，更好的写法见 5.4 节

③ 树形结构（分类、评论、部门）

— 商品分类：一级分类 → 二级分类
select c1.name as 一级, c2.name as 二级
from category c1, category c2
where c1.id = c2.parent_id;

④ 找出「连续 N 天都有记录」的数据（登录、打卡）

— 找连续两天都登录的用户
select distinct a.user_id
from login a, login b
where a.user_id = b.user_id
and b.login_date = date_add(a.login_date, interval 1 day);

自连接的本质：把「一行内的多个角色」摊平成「多行」。一旦你想通「一张表可以同时扮演 A 和 B」，很多难题就迎刃而解了。

4.6 补全：自连接的性能陷阱

自连接的笛卡尔积是「行数的平方」：

表行数自连接笛卡尔积

14	196
10,000	100,000,000（一亿）
1,000,000	一万亿

所以：

连接条件上的列必须有索引（比如 emp.mgr、category.parent_id）

大表自连接要极其谨慎，先想清楚能不能用窗口函数（8.0+）替代

— 用窗口函数替代自连接（8.0+），效率高得多
select ename, sal, deptno,
rank() over (partition by deptno order by sal desc) as rk
from emp;

5. 子查询：SQL 里的「套娃」

5.1 子查询的分类地图

先给你一张地图，后面就好定位了：

子查询
├── 按返回结果分
│ ├── 标量子查询（1 行 1 列） → 可以当「一个值」用
│ ├── 列子查询（多行 1 列） → 配 in / any / all
│ └── 行子查询（1 行多列） → 配 (a, b) = (…)
│
└── 按出现位置分
├── WHERE 子句里
├── FROM 子句里（派生表 / 临时表）
└── SELECT 列表里（标量子查询）

子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询。

5.2 单行子查询

返回一行记录的子查询。

— 显示 SMITH 同一部门的员工
select * from EMP where deptno = (select deptno from EMP where ename = 'smith');

结果：

+——-+——–+———+——+————+———+——+——–+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+——-+——–+———+——+————+———+——+——–+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+——-+——–+———+——+————+———+——+——–+

⚠️ 这里有个「隐性假设」： ename = 'smith' 只能返回一行。

如果 SMITH 是同名，子查询返回多行，就会报错：

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

这是单行子查询最经典的报错。解决方案见 5.3 节（改用 in）。

补全：判断子查询结果为空时

— 如果子查询返回 NULL，外层 `= NULL` 永远为假，结果是空集
select * from emp where deptno = (select deptno from emp where ename = '不存在的人');
— Empty set

— 用 <=> 或 IS NULL 也无法直接套用（因为 NULL <=> NULL 为真，但这里的 NULL 是子查询结果）

🔗 前后照应：第 06 篇 3.4.9 节讲 <=> 时说过「= NULL 永远查不出」。这里再加深一层：不只字面 NULL 会这样，「子查询返回 NULL」也会。所以用子查询做 where 列 = (子查询) 时，一定要确认子查询一定有且只有一行结果。

5.3 多行子查询：in / all / any

5.3.1 in：等于「列表里的任意一个」

查询和 10 号部门的工作岗位相同的雇员的名字、岗位、工资、部门号，但是不包含 10 号部门自己的。

select ename, job, sal, deptno
from emp
where job in (select distinct job from emp where deptno = 10)
and deptno <> 10;

结果：

+——-+———+———+——–+
| ename | job | sal | deptno |
+——-+———+———+——–+
| SMITH | CLERK | 800.00 | 20 |
| JONES | MANAGER | 2975.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| ADAMS | CLERK | 1100.00 | 20 |
| JAMES | CLERK | 950.00 | 30 |
| FORD | ANALYST | 3000.00 | 20 |
+——-+———+———+——–+

拆解：

① 子查询做了什么？

select distinct job from emp where deptno = 10;
— CLERK, MANAGER, ANALYST, PRESIDENT

10 号部门有 4 种岗位（CLARK=MANAGER、KING=PRESIDENT、MILLER=CLERK，还有 SCOTT/FORD 在 20 号？其实是 4 种：CLERK/MANAGER/PRESIDENT… 实际结果依数据而定）。

② 为什么子查询里要加 distinct？

其实可以不加（in 会自动去重，重复的值对结果没影响），但加上更清晰、也更省内存（子查询结果集更小）。

③ deptno <> 10 为什么必须加？

因为需求明确「不包含 10 号部门自己」。如果把 10 号部门的 CLARK、KING、MILLER 也算进来，就违背题意了。

⚠️ 注意一个反直觉的点：这条 SQL 没有排除 SMITH 本人（SMITH 在 20 号部门，岗位 CLERK，符合条件）。题目只要求「排除 10 号部门」，没要求「排除某个人」。做题时一定要逐字读题。

5.3.2 all：比「全部」都大

显示工资比 30 号部门的所有员工的工资都高的员工的姓名、工资和部门号。

select ename, sal, deptno from EMP where sal > all(select sal from EMP where deptno = 30);

结果：

+——-+———+——–+
| ename | sal | deptno |
+——-+———+——–+
| JONES | 2975.00 | 20 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| FORD | 3000.00 | 20 |
+——-+———+——–+

> all(子查询) 的语义：比子查询返回的「每一个值」都大。

等价于什么？

— 等价于：大于最大值
where sal > (select max(sal) from emp where deptno = 30)
— 30 号部门最高工资是 2850（BLAKE），所以结果都是 > 2850 的人

⚠️ 注意：> all 的逻辑等价于 > max，但要注意子查询为空的情况：

— 如果子查询返回空集
where sal > all(空集) — 恒为 TRUE（因为「对所有 x 都成立」在空集上是真空成立的！）
where sal > max(空集) — max 返回 NULL，sal > NULL 恒为 NULL→FALSE

两者在「子查询为空」时行为相反！这是个极端的边界情况，但面试有时会问。

5.3.3 any：比「任意一个」大

显示工资比 30 号部门的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）。

select ename, sal, deptno from EMP where sal > any(select sal from EMP where deptno = 30);

结果：

+——–+———+——–+
| ename | sal | deptno |
+——–+———+——–+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| FORD | 3000.00 | 20 |
+——–+———+——–+

> any(子查询) 的语义：比子查询返回的「至少一个值」大。

等价于什么？

— 等价于：大于最小值
where sal > (select min(sal) from emp where deptno = 30)
— 30 号部门最低工资是 950（JAMES），所以 > 950 的人全进来了

5.3.4 【核心对照表】三者的等价关系（背下来）

写法语义等价于记忆法

> all	比全部都大	> max()	all = 最严，要赢所有人
> any	比某个大（≥1 个）	> min()	any = 最松，赢一个就行
= any	等于任意一个	in	= any 就是 in
<> all	不等于任何一个	not in	<> all 就是 not in
> all（空集）	真空成立	TRUE	边界坑
> any（空集）	不成立	FALSE	边界坑

再补三个：

写法语义等价于

< all	比全部都小	< min()
< any	比某个小	< max()
>= all / <= any	同理	>= max() / <= min()

some 是 any 的同义词（标准 SQL 里两者等价，MySQL 都支持）。

⚠️ 最重要的一个坑：any 和 some 都是「只要有一个满足」，不是「全部满足」。

很多人看到 "any" 以为是「任意（随便一个都行）」，理解成了 all。记住：

any = 存在一个（∃），all = 所有（∀）。

5.4 多列子查询

单行子查询是指子查询只返回单列、单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的。而多列子查询则是指查询返回多个列数据的子查询语句。

案例：查询和 SMITH 的部门和岗位完全相同的所有雇员，不含 SMITH 本人

select ename from EMP
where (deptno, job) = (select deptno, job from EMP where ename = 'SMITH')
and ename <> 'SMITH';

结果：

+——-+
| ename |
+——-+
| ADAMS |
+——-+

这是「行值构造器（Row Constructor）」语法：

(a, b) = (子查询返回的两列)

它等价于：

where deptno = (select deptno from emp where ename='SMITH')
and job = (select job from emp where ename='SMITH')

但一行写法更清晰、也只跑一次子查询。

⚠️ 三个语法限制：

① 括号里的列数必须一致

— ❌ 列数不匹配
where (deptno, job, sal) = (select deptno, job from emp where ename='SMITH');
— ERROR 1241 (21000): Operand should contain 3 column(s)

② 只能用在 = 和 <> 上（MySQL 不支持 (a,b) > (c,d) 这种行比较）

③ in 也可以配多列（8.0 支持得更好）

— 找出「部门+岗位」组合在某个集合里的员工
where (deptno, job) in ((10,'CLERK'), (20,'ANALYST'))

5.5 【重点】在 FROM 子句中使用子查询（派生表）

子查询语句出现在 FROM 子句中。这里要用到数据查询的技巧：把一个子查询当做一个临时表使用。

这是全篇最有价值的一节。因为它解决了一整类「单表查询做不到」的问题。

5.5.1 案例一：显示每个高于自己部门平均工资的员工

需求本质：

对于每一个员工：
① 算出「他所在部门」的平均工资
② 比较：他的工资 > 部门平均？

难点：第 ① 步的「部门平均工资」是「按部门分组」的聚合结果，而第 ② 步又要回到「逐行」比较。两次操作的粒度不一样。

解法：把「部门平均工资」做成一张临时表，再和 emp 连接。

— 获取各个部门的平均工资，将其看作临时表
select ename, deptno, sal, format(asal, 2)
from EMP,
(select avg(sal) asal, deptno dt from EMP group by deptno) tmp
where EMP.sal > tmp.asal and EMP.deptno = tmp.dt;

结果：

+——-+——–+———+———————+
| ename | deptno | sal | format(asal, 2) |
+——-+——–+———+———————+
| ALLEN | 30 | 1600.00 | 1566.67 |
| JONES | 20 | 2975.00 | 2175.00 |
| BLAKE | 30 | 2850.00 | 1566.67 |
| SCOTT | 20 | 3000.00 | 2175.00 |
| KING | 10 | 5000.00 | 2916.67 |
| FORD | 20 | 3000.00 | 2175.00 |
+——-+——–+———+———————+

拆解这个「临时表」：

(select avg(sal) asal, deptno dt from EMP group by deptno) tmp
— 这个子查询的结果：
— asal=2916.67, dt=10
— asal=2175.00, dt=20
— asal=1566.67, dt=30

它是一张「只有 3 行」的表：每个部门一行。然后拿它和 14 行的 emp 做连接，emp.deptno = tmp.dt。

为什么这样就能解决问题？因为**「所有部门平均工资」这张小表，被广播到了每一行员工记录上**：

SMITH(20, 800) ←→ 部门20的平均2175 → 800 > 2175? 否
JONES(20, 2975) ←→ 部门20的平均2175 → 2975 > 2175? 是 ✅
BLAKE(30, 2850) ←→ 部门30的平均1566.67 → 是 ✅

⭐ 这是「派生表」最重要的思想：

当「聚合结果」和「明细行」需要在同一行里比较时，把聚合结果做成一张临时表，再 JOIN 回原表。

5.5.2 案例二：查找每个部门工资最高的人

select EMP.ename, EMP.sal, EMP.deptno, ms
from EMP,
(select max(sal) ms, deptno from EMP group by deptno) tmp
where EMP.deptno = tmp.deptno and EMP.sal = tmp.ms;

结果：

+——-+———+——–+———+
| ename | sal | deptno | ms |
+——-+———+——–+———+
| KING | 5000.00 | 10 | 5000.00 |
| SCOTT | 3000.00 | 20 | 3000.00 |
| FORD | 3000.00 | 20 | 3000.00 |
| BLAKE | 2850.00 | 30 | 2850.00 |
+——-+———+——–+———+

注意 20 号部门出现了两条（SCOTT 和 FORD 都是 3000）——因为它们并列最高，这符合语义。

同样的思想：把「每个部门最高工资」做成 3 行的临时表，再 join 回来。

5.5.3 案例三：显示每个部门的信息和人员数量

这是全篇最重要的一题，因为两种写法会得出「不同的结果」，而课程只给了前两种——都不是最全的。

方法 1：使用多表连接 + group by

select DEPT.dname, DEPT.deptno, DEPT.loc, count(*) '部门人数'
from EMP, DEPT
where EMP.deptno = DEPT.deptno
group by DEPT.deptno, DEPT.dname, DEPT.loc;

结果：

+————+——–+———-+———-+
| dname | deptno | loc | 部门人数 |
+————+——–+———-+———-+
| ACCOUNTING | 10 | NEW YORK | 3 |
| RESEARCH | 20 | DALLAS | 5 |
| SALES | 30 | CHICAGO | 6 |
+————+——–+———-+———-+

只有 3 个部门！40 号 OPERATIONS 部门消失了。

为什么？因为 where EMP.deptno = DEPT.deptno 是内连接——没有员工的部门，在 emp 里找不到匹配行，就被过滤掉了。

方法 2：使用子查询（派生表）

— 1. 对 EMP 表进行人员统计
select count(*), deptno from EMP group by deptno;

— 2. 将上面的表看作临时表
select DEPT.deptno, dname, mycnt, loc
from DEPT,
(select count(*) mycnt, deptno from EMP group by deptno) tmp
where DEPT.deptno = tmp.deptno;

结果和方法 1 完全一样，40 号部门还是不在。

为什么？因为 from DEPT, (…) + where DEPT.deptno = tmp.deptno 依然是内连接——临时表里没有 40 号部门（因为 emp 里没人在 40 号），所以 40 号对不上，又被过滤了。

⭐ 5.5.4 那正确答案是什么？（这是全文的钩子）

要显示「所有部门（包括没有员工的）」，必须用「外连接」：

select d.deptno, d.dname, d.loc, count(e.empno) as 部门人数
from dept d
left join emp e on d.deptno = e.deptno
group by d.deptno, d.dname, d.loc;

结果（这才是业务上真正要的）：

+——–+————+———-+———-+
| deptno | dname | loc | 部门人数 |
+——–+————+———-+———-+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 | ← 有了！
+——–+————+———-+———-+

注意：count(e.empno) 而不是 count(*)！

因为 left join 给 40 号部门补了一行「全 NULL 的 emp 记录」，count(*) 会把这一行算成 1（错！），而 count(e.empno) 只统计非 NULL 的 empno，得到 0（对！）。

🔗 前后照应 + 预告：这里第一次出现了「内连接会丢数据」这个问题，而解法是 left join。这正是下一篇《内外连接》要彻底讲透的东西。

而且我把这一题的三个版本放在一起，就是想让你看清一件事：课件上的 SQL 不一定是最优解。方法 1 和方法 2 都对「语法」，但都不符合「显示每个部门」这个需求——因为它们都漏掉了 40 号部门。

能看出「课件这里的写法丢数据」，才算真的读懂了。

5.6 补全：exists / not exists（课件没讲）

子查询还有一大类用法：exists。

— 找出「有下属」的经理
select * from emp e
where exists (select 1 from emp w where w.mgr = e.empno);

— 找出「没有下属」的员工
select * from emp e
where not exists (select 1 from emp w where w.mgr = e.empno);

exists 的语义：子查询是否「有结果」（不关心结果是什么，只看有没有行）。

exists vs in 的关键区别：

对比inexists

子查询返回什么	一个值集合	只判断有无行
本质	先算出子查询结果，再拿外层值去匹配	相关子查询：外层每一行都跑一次子查询
子查询大时	慢（要物化整个结果集）	快（找到一个就返回）
写法	where x in (select y from t)	where exists (select 1 from t where t.y = x)

⚠️ exists 是「相关子查询（correlated subquery）」——它引用了外层的 e.empno，所以外层每一行都会执行一次子查询。听起来很慢，但因为「找到一个就停」，实际往往比 in 快。

现代 MySQL 优化器会把很多 in 和 exists 优化成同样的执行计划（半连接 semi-join 优化），所以不要背「谁更快」，要用 EXPLAIN 看实际情况。

还有 not exists 的一个重要优势：它不会被 NULL 坑。

🔗 前后照应：第 06 篇 3.4.3 节讲过 NOT IN (…, NULL, …) 会永远返回空。而 not exists 不会——因为它判断的是「行是否存在」，不是「值是否相等」。

— ❌ 如果子查询里有 NULL，一条都查不出来
where empno not in (select mgr from emp)

— ✅ 安全
where not exists (select 1 from emp w where w.mgr = e.empno)

这是 not exists 相对 not in 最大的实用价值。

6. 合并查询：UNION 与 UNION ALL

在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union、union all。

6.1 union：并集 + 自动去重

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

select ename, sal, job from EMP where sal > 2500
union
select ename, sal, job from EMP where job = 'MANAGER';

结果（6 行）：

+——-+———+———–+
| ename | sal | job |
+——-+———+———–+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| CLARK | 2450.00 | MANAGER |
+——-+———+———–+

分析：

第一个查询（sal > 2500）：JONES、SCOTT、BLAKE、KING、FORD → 5 行
第二个查询（job = 'MANAGER'）：JONES、BLAKE、CLARK → 3 行
合并：5 + 3 = 8 行，但 JONES 和 BLAKE 重复了，去重后 → 6 行

注意 CLARK 的出现：他的 sal = 2450，不满足第一个条件，但因为他是 MANAGER，被第二个查询捞进来了。这正是这道题的目的——用 union 表达「或」的逻辑，但比 where sal > 2500 or job = 'MANAGER' 更灵活（每段可以有自己的表、自己的排序）。

6.2 union all：并集 + 保留重复

select ename, sal, job from EMP where sal > 2500
union all
select ename, sal, job from EMP where job = 'MANAGER';

结果（8 行）：

+——-+———+———–+
| ename | sal | job |
+——-+———+———–+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| JONES | 2975.00 | MANAGER | ← 重复
| BLAKE | 2850.00 | MANAGER | ← 重复
| CLARK | 2450.00 | MANAGER |
+——-+———+———–+

8 行，重复的 JONES 和 BLAKE 都保留了。

6.3 【补全】union 的 6 个硬规则（课件没讲，但一定会踩）

规则 1：两个查询的列数必须相同

— ❌ ERROR 1222: The used SELECT statements have a different number of columns
select empno, ename from emp
union
select deptno from dept;

规则 2：列类型要兼容，最终以「第一个查询」的列类型为准

select 1, 'a'
union
select '2', 'b';
— 结果里 '2' 会变成 2（按第一个查询的 int 类型转换）

规则 3：结果集的列名取「第一个查询」的列名

select ename as n1 from emp where empno = 7369
union
select dname as n2 from dept where deptno = 10;
— 结果列名是 n1，不是 n2

规则 4：order by 只能写在最后，且作用于整个合并结果

— ❌ 报错
select * from emp order by sal
union
select * from emp order by sal;

— ✅ 正确
select * from emp where deptno = 10
union
select * from emp where deptno = 20
order by sal desc;

规则 5：单个 select 里的 order by / limit 要用括号包起来

(select * from emp order by sal desc limit 3)
union all
(select * from emp order by sal asc limit 3);

⚠️ 但要注意：这种写法里，子句的 order by + limit 必须成套出现，否则 MySQL 可能忽略 order by（因为「没 limit 的 order by 在子查询里无意义」）。

规则 6：union 会隐式排序（去重的代价），union all 不会

操作去重是否隐式排序性能

union all	❌	❌	🚀 快（直接拼接）
union	✅	✅（为了去重）	🐢 慢（要建临时表 + 排序）

⚠️ 这是最重要的一条：union 比 union all 慢，因为它要做「去重」——而 MySQL 的去重实现方式是「排序后相邻比较」或「建哈希表」，无论哪种都要额外开销。

所以：如果确定两个结果集不会重复，永远用 union all。

🔗 前后照应：这和第 06 篇讲 DISTINCT 的代价是同一个道理（去重必须排序/哈希）。union = union all + distinct。

6.4 补全：什么时候必须用 union，而不能用 or？

有三种场景，or 表达不了，只能用 union：

① 两个查询来自不同的表

select name, 'emp' as src from emp where sal > 3000
union all
select dname, 'dept' as src from dept where loc = 'BOSTON';

② 两个查询需要各自的排序和条数

— 「各科目前 3 名」这种需求
(select * from score where subject = 'chinese' order by score desc limit 3)
union all
(select * from score where subject = 'math' order by score desc limit 3);

③ 两个查询的过滤条件差异极大，or 会让索引失效

— ❌ 用 or：两张表的索引都可能用不上
select * from a join b on … where a.x = 1 or b.y = 2;

— ✅ 用 union all：各自走各自索引
select * from a join b on … where a.x = 1
union all
select * from a join b on … where b.y = 2 and a.x <> 1;

💡 union 是「SQL 里的拆解术」：把一个大 or 拆成两个各走索引的小查询，再用 union all 拼回来。这是老程序员常用的优化手法。

7. 补全：多表查询的执行顺序与性能思维

7.1 多表查询的执行顺序

把第 06 篇那张单表执行顺序图扩展一下：

① FROM / JOIN ← 先做笛卡尔积（或按 JOIN 条件配对）
② ON ← 外连接的行匹配条件
③ WHERE ← 逐行过滤
④ GROUP BY
⑤ HAVING
⑥ SELECT
⑦ DISTINCT
⑧ ORDER BY
⑨ LIMIT

注意 FROM 和 ON 在最前面——这解释了为什么「连接条件」要写在 ON 里而不是 WHERE 里（除了外连接语义不同之外，ON 更早执行）。

7.2 三表连接怎么写？

从左到右，一层层拼：

— 需求：显示员工名、部门名、工资等级
select e.ename, d.dname, s.grade
from emp e
join dept d on e.deptno = d.deptno
join salgrade s on e.sal between s.losal and s.hisal;

执行过程：

① emp join dept → 14 行
② 结果 join salgrade → 14 行

⚠️ 顺序很重要：emp 先和 dept 连（14 行），再和 salgrade 连（还是 14 行）。如果顺序反了（emp join salgrade 先连），中间结果也是 14 行。小表优先、过滤性强的先连，是优化的基本原则。

7.3 性能三条铁律

铁律一：连接条件的字段必须有索引

— emp.deptno 上有索引，连接才会快
alter table emp add index idx_deptno (deptno);

没有索引会怎样？ MySQL 会用「嵌套循环连接（Nested-Loop Join）」，外层每一行都要去内层全表扫一遍：

14 行（emp）× 全表扫 dept（4 行）= 56 次比较 ← 小表没事
100 万行 × 全表扫 100 万行 = 一万亿次比较 ← 直接死

铁律二：小表驱动大表

— 假设 dept 4 行，emp 100 万行
select * from dept d join emp e on d.deptno = e.deptno;
— 外层 4 行，每行去 emp 的索引里找 → 4 次索引查找

优化器通常会自动选小表做「驱动表」，但你可以用 STRAIGHT_JOIN 强制指定。

铁律三：能早过滤就早过滤

— ❌ 先连接 100 万行，再筛 10 行
select * from emp e join dept d on e.deptno = d.deptno
where d.dname = 'SALES' and e.sal > 3000;

— ✅ 优化器一般会重排，但显式写清楚更保险
select * from
(select * from emp where sal > 3000) e
join
(select * from dept where dname = 'SALES') d
on e.deptno = d.deptno;

🔗 前后照应：第 07 篇 6.3 节讲的「让字段裸奔」原则，在多表查询里同样适用——on e.deptno = d.deptno 里两边都是裸字段，索引才能用。

7.4 补全：子查询 vs JOIN，谁更快？

这是面试高频题，但没有绝对答案。

场景推荐原因

只需要「是否存在」	exists	找到一个就停
需要子查询的多个列	JOIN	子查询只能返回一列（除行子查询）
子查询结果很小	子查询	先算出小结果集，再匹配
子查询结果很大	JOIN	子查询物化开销大
需要保留未匹配的行	LEFT JOIN	子查询做不到（not in 有 NULL 坑）
只需要聚合值（max/avg）	子查询	语义更直接

现代 MySQL 优化器（5.7+）会把很多子查询「改写」成 JOIN（这叫 subquery materialization / semi-join 优化）。所以：

优先考虑「哪个写法更清晰」，再看 EXPLAIN 决定要不要改。不要为了「理论上更快」把 SQL 写成人类读不懂的样子。

8. 实战 OJ

8.1 查找所有员工入职时的薪水情况

给出 emp_no 以及 salary，并按照 emp_no 进行逆序。

select e.emp_no, s.salary
from employees e
join salaries s on e.emp_no = s.emp_no
where e.hire_date = s.from_date
order by e.emp_no desc;

考点：多表连接 + 时间字段的等值匹配。from_date 表示「该薪资从哪天开始生效」，入职那天的薪资就是 from_date = hire_date 的那条记录。

8.2 针对库中的所有表，生成 select count(*) from tableName

select concat('select count(*) from ', table_name, ';') as sql_stmt
from information_schema.tables
where table_schema = '你的库名' and table_type = 'BASE TABLE';

这题非常经典，考点有三层：

① information_schema.tables：MySQL 的「元数据表」，存着所有库、所有表的信息。这是「用 SQL 生成 SQL」的关键。

② concat 拼接字符串（第 07 篇讲过的函数）。

③ table_type = 'BASE TABLE' 过滤掉视图（VIEW），否则会生成对视图的 count —— 虽然也能跑，但通常不是你要的。

输出结果大概长这样：

select count(*) from emp;
select count(*) from dept;
select count(*) from salgrade;

然后你把结果复制出来执行一遍，就得到所有表的行数统计。这在「数据迁移前评估数据量」「排查空表」时非常实用。

8.3 获取所有非 manager 的员工 emp_no

— 解法一：not in
select emp_no from employees
where emp_no not in (select emp_no from dept_manager);

— 解法二：left join + is null（推荐，性能更好）
select e.emp_no
from employees e
left join dept_manager dm on e.emp_no = dm.emp_no
where dm.emp_no is null;

— 解法三：not exists
select emp_no from employees e
where not exists (select 1 from dept_manager dm where dm.emp_no = e.emp_no);

⚠️ 解法一的隐患：dept_manager.emp_no 如果是可空的且含 NULL，not in 会返回空集。

🔗 前后照应：第 06 篇 3.4.3 节讲 NOT IN + NULL 的坑，这里是实战。dept_manager.emp_no 通常是主键（非空），所以安全；但如果换成一个可空的列，解法一立刻翻车。

所以生产环境我更推荐解法二（left join + is null）——它天然免疫 NULL 问题。

8.4 获取所有员工当前的 manager（manager 是自己则不显示）

select e.emp_no as 员工, m.emp_no as 经理
from dept_emp e
join dept_manager m on e.dept_no = m.dept_no
where e.to_date = '9999-01-01'
and m.to_date = '9999-01-01'
and e.emp_no <> m.emp_no;

考点：

① to_date = '9999-01-01' 是这套题表的「当前记录」标记（历史记录会有结束日期）。

② e.emp_no <> m.emp_no 排除「自己管自己」的情况。

③ 这里其实隐含了「同一部门」的语义——「员工的部门 = 经理的部门」，用 e.dept_no = m.dept_no 表达。

8.5 补全：再来三道经典「复合查询」题

① 查出每个部门工资第二高的人（不用窗口函数）

select e.deptno, e.ename, e.sal
from emp e
where 2 = (select count(distinct e2.sal) from emp e2
where e2.deptno = e.deptno and e2.sal > e.sal);

这是一个「相关子查询」——对每一行 e，去数「同部门里比我工资高的人有几个」，等于 2 的就是第三高（以此类推）。这是「相关子查询」的经典用法。

② 查出每个部门中「工资并列最高」的所有人

select e.ename, e.sal, e.deptno
from emp e
join (select deptno, max(sal) as ms from emp group by deptno) t
on e.deptno = t.deptno and e.sal = t.ms;

这就是 5.5.2 节那个案例，换个写法。

③ 查出「至少有一个员工」的部门的所有信息

select * from dept d
where exists (select 1 from emp e where e.deptno = d.deptno);

注意这和 select distinct d.* from dept d join emp e on … 等价，但 exists 不会产生重复行（distinct 才要）。

9. 报错速查

错误码报错文本原因解决

1052	Column 'xxx' in field list is ambiguous	多表有同名列，没加表名前缀	写表名.列名
1066	Not unique table/alias: 'emp'	自连接没起别名 / 两个别名撞了	emp leader, emp worker
1111	Invalid use of group function	WHERE 里用了聚合函数	改用子查询或 HAVING
1241	Operand should contain N column(s)	行子查询列数不匹配	对齐 (a,b) 的列数
1242	Subquery returns more than 1 row	单行子查询返回了多行	改 in / 加 limit 1 / 加更严的条件
1248	Every derived table must have its own alias	from (子查询) 没起别名	from (…) tmp
1222	The used SELECT statements have a different number of columns	union 两边列数不一致	补列（select null 占位）
1093	You can't specify target table for update in FROM clause	update/delete 的子查询引用了同一张表	套一层派生表 (select … from t) tmp

🔗 1248 这个报错特别常见：from (select …) 后面必须跟一个别名（哪怕你压根不用它）。

— ❌ ERROR 1248
select * from (select deptno, avg(sal) from emp group by deptno);

— ✅
select * from (select deptno, avg(sal) from emp group by deptno) tmp;

10. 面试高频 12 问

Q1：笛卡尔积是什么？怎么避免？两张表的所有行做「两两配对」的结果，行数 = 两张表行数之积。通过 where / on 加连接条件来过滤掉不匹配的组合。

Q2：from a, b where … 和 a join b on … 有什么区别？完全没有区别，前者叫隐式连接，后者叫显式连接，优化器会生成相同计划。区别只在可读性（join 更好）和「外连接时必须用 join … on」。

Q3：什么是自连接？什么时候必须用？把同一张表起两个别名当两张表用。当你需要「同一张表内的两行做比较」或「层级关系（员工-领导）」时必须用。

Q4：in、any、all 的关系？ = any ≡ in；<> all ≡ not in；> all ≡ > max()；> any ≡ > min()。

Q5：exists 和 in 哪个快？不绝对。in 适合「子查询结果小」，exists 适合「子查询结果大 / 外层小」。现代优化器常把两者优化成同一计划，用 EXPLAIN 判断。

Q6：not in 为什么可能返回空集？怎么避免？因为 x NOT IN (1, 2, NULL) 展开后含 x <> NULL，结果恒为 NULL（非 TRUE）。解法：子查询里加 where 列 is not null，或改用 not exists。

Q7：from 子句里的子查询（派生表）有什么限制？必须起别名（否则 1248）；MySQL 5.7 会物化成临时表，8.0 有 derived merge 优化（能合并的直接合并，不能的才物化）。

Q8：union 和 union all 的区别？ union 去重（隐含排序/哈希，慢），union all 不去重（直接拼接，快）。能确定不重复时永远用 union all。

Q9：union 能用 order by 吗？能，但只能写在最后一个 select 之后，且作用于整个合并结果。要对单个子查询排序，需用括号包起来并配 limit。

Q10：多列子查询怎么用？用「行值构造器」：where (a, b) = (select x, y from t)。注意列数必须一致，且只支持 = / <> 比较。

Q11：怎么查「每个部门工资高于本部门平均工资的人」？把「部门平均工资」做成派生表（select avg(sal), deptno from emp group by deptno），再 JOIN 回 emp，条件 emp.sal > tmp.avg AND emp.deptno = tmp.deptno。

Q12：三表连接怎么写？执行顺序是怎样的？从左到右逐层 JOIN：a join b on … join c on …。执行时先 a join b 得到中间结果，再用这个结果去 join c。连接字段必须有索引，小表驱动大表，能早过滤就早过滤。

11. 总结：一张图 + 一句口诀

┌────────────────────────────────┐
│ 复合查询的四种武器 │
└────────────────────────────────┘
│
┌──────────────┬──────────────┼──────────────┬────────────────┐
│ │ │ │ │
【多表查询】【自连接】【子查询】【合并查询】【隐藏 boss】
│ │ │ │ │
from a, b 同一表起 WHERE 里 union 笛卡尔积
join on 两个别名 ├ 单行(= max) union all = 行数之积
where 条件 ├ 多行(in/any/all)
├ 多列((a,b)=(x,y))
└ FROM 里(派生表)

一句口诀：

「先想笛卡尔积，再加连接条件；同表比较起别名，聚合结果做临时表。」

展开：

半句含义

先想笛卡尔积	任何多表查询，先在脑子里画出「行数之积」，你就知道为什么必须加条件
再加连接条件	where / on 负责从笛卡尔积里筛出「对的配对」
同表比较起别名	自连接必须 emp leader, emp worker，否则报 1066
聚合结果做临时表	「高于部门平均」这类需求，把聚合结果放 FROM 里当小表，再 JOIN 回来

最后，把这一篇和前三篇串起来：

篇主题一句话

05	表的约束	约束是门禁，脏数据进不来
06	增删改查	执行顺序决定一切
07	内置函数	函数别碰字段，索引就活了
08	复合查询	多表 = 笛卡尔积 + 筛选，聚合结果做临时表

而这一篇埋下的那颗「40 号部门消失了」的雷，下一篇就要引爆：

内连接只保留「两边都对得上」的行。要留住那些「对不上」的行（比如没有员工的部门、没有成绩的学生），你必须用外连接。

——这就是《内连接与外连接》要解决的问题。我们下一篇见。

附：一键可执行完整脚本

— ============================================================
— MySQL 复合查询 · 全功能演示脚本
— 环境：MySQL 5.7 / 8.0 均可
— ============================================================
drop database if exists multi_demo;
create database multi_demo default character set utf8mb4;
use multi_demo;

— ———- 建表 ———-
create table dept (
deptno int primary key,
dname varchar(14),
loc varchar(13)
);
insert into dept values
(10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

create table emp (
empno int primary key,
ename varchar(10),
job varchar(9),
mgr int,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int
);
insert into emp values
(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);

create table salgrade (grade int, losal int, hisal int);
insert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

— ———- 2. 基本查询回顾 ———-
select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
select * from emp order by deptno, sal desc;
select ename, sal * 12 + ifnull(comm, 0) as '年薪' from emp order by 年薪 desc;
select ename, job from emp where sal = (select max(sal) from emp);
select ename, sal from emp where sal > (select avg(sal) from emp);
select deptno, format(avg(sal), 2), max(sal) from emp group by deptno;
select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;
select job, count(*), format(avg(sal), 2) from emp group by job;

— ———- 3. 多表查询 ———-
select count(*) from emp, dept; — 56（笛卡尔积）
select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;
select ename, sal, dname from EMP, DEPT where EMP.deptno = DEPT.deptno and DEPT.deptno = 10;
select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

— ———- 4. 自连接 ———-
— 子查询解法
select empno, ename from emp where emp.empno = (select mgr from emp where ename = 'FORD');
— 自连接解法
select leader.empno, leader.ename
from emp leader, emp worker
where leader.empno = worker.mgr and worker.ename = 'FORD';
— 多级：员工-领导-领导的领导
select e1.ename as 员工, e2.ename as 领导, e3.ename as 上级
from emp e1
left join emp e2 on e1.mgr = e2.empno
left join emp e3 on e2.mgr = e3.empno;

— ———- 5. 子查询 ———-
— 5.1 单行
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
— 5.2 多行：in / all / any
select ename, job, sal, deptno from emp
where job in (select distinct job from emp where deptno = 10) and deptno <> 10;
select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30);
select ename, sal, deptno from emp where sal > any(select sal from emp where deptno = 30);
— 5.3 多列
select ename from emp
where (deptno, job) = (select deptno, job from emp where ename = 'SMITH')
and ename <> 'SMITH';

— 5.4 FROM 里的子查询（派生表）
— 高于本部门平均工资
select ename, deptno, sal, format(asal, 2)
from emp, (select avg(sal) asal, deptno dt from emp group by deptno) tmp
where emp.sal > tmp.asal and emp.deptno = tmp.dt;

— 每个部门工资最高的人
select emp.ename, emp.sal, emp.deptno, ms
from emp, (select max(sal) ms, deptno from emp group by deptno) tmp
where emp.deptno = tmp.deptno and emp.sal = tmp.ms;

— 部门信息 + 人数（方法1：内连接，会丢 40 号部门）
select DEPT.dname, DEPT.deptno, DEPT.loc, count(*) '部门人数'
from EMP, DEPT
where EMP.deptno = DEPT.deptno
group by DEPT.deptno, DEPT.dname, DEPT.loc;

— 部门信息 + 人数（方法2：派生表，同样丢 40 号部门）
select DEPT.deptno, dname, mycnt, loc
from DEPT, (select count(*) mycnt, deptno from EMP group by deptno) tmp
where DEPT.deptno = tmp.deptno;

— ⭐ 正确答案：外连接 + count(列)
select d.deptno, d.dname, d.loc, count(e.empno) as 部门人数
from dept d left join emp e on d.deptno = e.deptno
group by d.deptno, d.dname, d.loc;

— 5.6 exists / not exists
select * from emp e where exists (select 1 from emp w where w.mgr = e.empno);
select * from emp e where not exists (select 1 from emp w where w.mgr = e.empno);

— ———- 6. 合并查询 ———-
select ename, sal, job from EMP where sal > 2500
union
select ename, sal, job from EMP where job = 'MANAGER';