MySQL 复合查询：多表查询、子查询与合并查询

如果你已经学完了 SELECT、WHERE、GROUP BY、HAVING、ORDER BY 这些"单表查询"的十八般武艺，你会发现一个尴尬的事实：它们统统是对一张表在操作。可真实世界里，你要查的数据几乎不可能安安静静躺在一张表里——"员工在 EMP 表，部门名在 DEPT 表，工资级别在 SALGRADE 表"，企业的数据天生就是散落在多个仓库的。这时候，单表查询就顶不住了。

复合查询（Composite Query）就是解决这个问题的。它通常由三块拼图构成：多表查询（把多张表拼起来一起查）、子查询（把查询结果再喂给另一个查询）、以及合并查询（把多个查询结果拼成一列结果集）。这三块东西，是几乎所有数据库面试和实战题绕不开的核心考点，也是你必须敲到骨子里的基本功。

这篇文章，我们用一张经典的"公司管理系统"三张表（EMP、DEPT、SALGRADE）从头到尾把这些概念讲透。全程自然讲课风格，配合真实结果的展示。文末还给大家准备了一套自测题，每题都带详解答案，你可以学完自己测一测。

在开始之前，先做一件事：把下面这份建表、插数据的 SQL 在你的 MySQL 里跑一遍。后面所有例子我都默认这些数据已经就位。

动手前的准备：三张表和一份完整的建表素材

我们先看这三张表的职责。它们共同描述一个迷你公司的员工档案：

EMP 表：员工主表，记录每个员工的编号 EMPNO、姓名 ENAME、岗位 JOB、上级领导编号 MGR、入职日期 HIREDATE、月工资 SAL、提成 COMM、所在部门编号 DEPTNO。注意 MGR 存的其实是另一个员工的 EMPNO，也就是说"领导"本身也是 EMP 表里的一条记录——这个设定是后面讲自连接的伏笔。
DEPT 表：部门表，记录部门编号 DEPTNO、部门名 DNAME、部门所在地 LOC。
SALGRADE 表：工资级别表，记录级别 GRADE、以及这个级别对应的工资下限 LOSAL 和上限 HISAL。

一条数据的完整信息，往往要这三张表合力才能拼出来。比如你想知道"SCOTT 的部门叫什么、他的工资属于第几级"，光盯着 EMP 表是看不出来的——部门名在 DEPT，工资级别在 SALGRADE。

下面是完整的建表与插入脚本：

-- 注意：带外键时，删除顺序要先删引用方 EMP，再删被引用方 DEPT/SALGRADE
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS SALGRADE;
 
-- 建部门表
CREATE TABLE DEPT (
    DEPTNO INT PRIMARY KEY,      -- 部门编号，主键，唯一标识一个部门
    DNAME  VARCHAR(20),          -- 部门名称
    LOC    VARCHAR(30)           -- 部门所在地
);
 
-- 插入 4 个部门的数据
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(20),       -- 员工姓名
    JOB      VARCHAR(20),       -- 工作岗位
    MGR      INT,               -- 上级领导的 EMPNO（领导也是员工，所以指回本表）
    HIREDATE DATE,              -- 入职日期
    SAL      DECIMAL(7,2),      -- 月工资
    COMM     DECIMAL(7,2),      -- 提成/奖金（销售岗有，非销售岗为 NULL）
    DEPTNO   INT                -- 所在部门编号
);
 
-- 插入 14 名员工
INSERT INTO EMP VALUES
  (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),     -- SMITH 普通员工，部门20
  (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),-- ALLEN 销售，部门30，有提成
  (7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30), -- WARD 销售，部门30，有提成
  (7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),   -- JONES 经理，领导是 KING
  (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),-- MARTIN 销售，部门30，有提成
  (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),   -- BLAKE 经理，领导是 KING
  (7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),   -- CLARK 经理，部门10
  (7788,'SCOTT','ANALYST',7566,'1982-12-09',3000.00,NULL,20),   -- SCOTT 分析师，部门20
  (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),  -- KING 总裁，没有领导（MGR 为 NULL）
  (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30), -- TURNER 销售，部门30，提成为0
  (7876,'ADAMS','CLERK',7788,'1983-01-12',1100.00,NULL,20),     -- ADAMS 普通员工，部门20
  (7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),      -- JAMES 普通员工，部门30
  (7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),    -- FORD 分析师，部门20
  (7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);    -- MILLER 普通员工，部门10
 
-- 建工资级别表
CREATE TABLE SALGRADE (
    GRADE INT PRIMARY KEY,    -- 工资级别，1 最低，5 最高
    LOSAL INT,                -- 该级别工资下限（含）
    HISAL INT                 -- 该级别工资上限（含）
);
 
-- 插入 5 个级别
INSERT INTO SALGRADE VALUES
  (1,  700,  1200),   -- 1级：700~1200
  (2, 1201,  1400),   -- 2级：1201~1400
  (3, 1401,  2000),   -- 3级：1401~2000
  (4, 2001,  3000),   -- 4级：2001~3000
  (5, 3001,  9999);   -- 5级：3001 及以上

插完顺手跑一句 select count(*) from EMP;，能看到返回 14，说明 14 名员工都到位了。后面的每一个例子，你都可以大胆地"先猜结果再执行"，这是学 SQL 最有效的训练方式。

基本查询回顾：复合查询前的地基

在进入多表之前，我们先把单表上的"基本查询"这层地基夯实。下面这组练习全部只涉及 EMP 这一张表，但它们覆盖了复合查询里最常用到的几个骨干：WHERE 过滤、ORDER BY 排序、聚合函数 MAX/AVG/COUNT、GROUP BY 分组、HAVING 过滤分组、以及一个"子查询"的雏形。你正好可以借此检验自己是否真的掌握了，再决定对后面的内容投入多大的注意力。

练习一：查询工资高于 500 或岗位为 MANAGER 的雇员，同时要求姓名首字母为大写 J。

select * from EMP
where (sal>500 or job='MANAGER')   -- 两个条件用 or 连接：工资>500 或 岗位是MANAGER
  and ename like 'J%';             -- and 再叠加：姓名以 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 |
-- +-------+-------+---------+------+------------+---------+------+--------+

这里有个陷阱要提醒：ename like 'J%' 是大小写不敏感的（取决于 MySQL 的字符集排序规则，默认情况下不区分大小写），所以 'jones' 也一样能匹配到。我们例子里的 JONES 和 JAMES 天然满足。注意 where 里的运算顺序：and 优先级高于 or，所以原文等价于 (sal>500 or job='MANAGER') and ename like 'J%'，一旦你想表达别的意思，就必须自己加括号。

练习二：按照部门号升序、同部门内按工资降序排序。

select * from EMP
order by deptno, sal desc;   -- 先按 deptno 升序（默认），再按 sal 降序（desc）
-- 结果（节选部门20的部分）：
-- deptno 最小的10先来，内部 KING(5000) 在最前；再到20，内部 SCOTT/FORD(3000) 领先……

order by deptno, sal desc 的意思是：先按第一个字段 deptno 升序排；当 deptno 相同时，再按第二个字段 sal 降序排。整条语句里 desc 只修饰离它最近的 sal，deptno 依然是升序。

练习三：使用年薪进行降序排序。年薪 = 月薪 × 12 + 提成；注意很多人会有提成是 NULL，NULL 直接参与加法会得到 NULL，所以要用 ifnull(comm,0) 把 NULL 当作 0 处理。

select ename,
       sal*12+ifnull(comm,0) as '年薪'   -- 年工资 = 月薪*12 + 奖金（NULL 当 0）
from EMP
order by 年薪 desc;                        -- 注意：MySQL 允许在 ORDER BY 里直接用别名
-- 结果：
-- +-------+----------+
-- | ename | 年薪     |
-- +-------+----------+
-- | KING  | 60000.00 |  5000*12，无提成，最高
-- | SCOTT | 36000.00 |
-- | FORD  | 36000.00 |
-- | ...   | ...      |

两个细节值得说。第一，order by 年薪 这里用的是别名（as 后的名字），MySQL 允许在 ORDER BY 和 HAVING 里引用别名，但要注意这只是 MySQl 的一个便利，别的数据库不一定默认支持。第二，ifnull(comm,0) 是全文章第一个"处理空值"的关键字：ifnull(a,b) 的意思是，如果 a 是 NULL，就返回 b，否则返回 a。这几乎是所有有"可空列"业务里必用的一招。

练习四：显示工资最高的员工的名字和工作岗位。这道题就是"子查询"的最朴素形态。

select ename, job
from EMP
where sal = (select max(sal) from EMP);   -- 括号里先算出最高工资，再拿它去比
-- 结果：
-- +-------+-----------+
-- | ename | job       |
-- +-------+-----------+
-- | KING  | PRESIDENT |   KING 工资5000，是全表最高
-- +-------+-----------+

这里括号里的 select max(sal) from EMP 就是所谓的子查询（也叫嵌套查询）。它的执行顺序是：先算出内部的最高工资 5000，把结果 5000 当作外面的比较基准，外层的 where sal = 5000 再筛出对应的人。你看，单表查询里其实已经埋下了"子查询"的种子。

练习五：显示工资高于平均工资的员工信息。

select ename, sal from EMP
where sal > (select avg(sal) from EMP);   -- 子查询先算平均工资，再比较每个 sal
-- 平均工资 = (800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100+950+3000+1300)/14 ≈ 2077.69
-- 结果：
-- +-------+---------+
-- | ename | sal     |
-- +-------+---------+
-- | JONES | 2975.00 |
-- | BLAKE | 2850.00 |
-- | CLARK | 2450.00 |
-- | SCOTT | 3000.00 |
-- | KING  | 5000.00 |
-- | FORD  | 3000.00 |
-- +-------+---------+

练习六：显示每个部门的平均工资和最高工资。这是 GROUP BY 分组的经典用法。

select deptno,
       format(avg(sal), 2),   -- 平均工资保留两位小数，format 返回的是字符串
       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 |
-- +--------+-------------------+----------+

group by deptno 的意思是"把部门编号相同的记录归成一堆"，每一堆产生一行结果。avg(sal) 和 max(sal) 是对这一堆里的 sal 分别求平均和取最大。部门 40 因为没有任何员工，不会出现在分组结果里——这在后面讲多表查询时会造成一个经典幻觉，留个印象。

练习七：显示平均工资低于 2000 的部门号和它的平均工资。要在"分组之后再过滤"，就得用 HAVING，不能再用 WHERE。

select deptno, avg(sal) as avg_sal   -- 给平均工资起别名 avg_sal
from EMP
group by deptno                      -- 先按部门分组
having avg_sal < 2000;               -- 分组后，只留平均工资低于2000的组
-- 结果：
-- +--------+----------+
-- | deptno | avg_sal  |
-- +--------+----------+
-- |     30 | 1566.67  |
-- +--------+----------+

这道题几乎是"为什么需要 HAVING"的最佳教材：WHERE 是在"分组前"对每一行过滤，你没法在 WHERE 里引用 avg(sal) 这种聚合结果——因为聚合结果要在分组完成后才算得出来。所以要么你写 having avg(sal)<2000，要么就用子查询套一层 where avg_sal < 2000。另外注意，having avg_sal<2000 也复用了别名 avg_sal，和 order by 别名 一样是 MySQL 的便利。

练习八：显示每种岗位的雇员总数和平均工资。

select job,                    -- 按岗位分组
       count(*),               -- 每组的人数
       format(avg(sal), 2)     -- 每组平均工资，保留两位小数
from EMP
group by job;                  -- 以 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           |
-- +-----------+----------+-------------------+

到这里，单表的基本查询就复习完了。如果你觉得这些轻松过关，就跟着我进入今天的重头戏——多表查询。

多表查询：让数据跨表说话

先抛一个直观的场景：显示每个雇员的姓名、工资，以及他所在的部门名字。姓名和工资在 EMP 表里，部门名 dname 却在 DEPT 表里。你单查任何一张表都凑不齐"三个字段都在一行"。怎么办？答案是多表查询。

其实你已经隐约知道答案了：把两张表"拼"在一张临时的大表里。这个"拼"的数学动作，就叫做笛卡尔积——它几乎是所有多表查询的地基，也是最容易踩坑的地方，我们必须先把它讲清楚。

什么是笛卡尔积

笛卡尔积是一个数学概念，用在数据库里，就是指"把表 A 的每一行，都和表 B 的每一行配成一对"。如果 EMP 有 14 行、DEPT 有 4 行，那么 EMP×DEPT 的笛卡尔积就是 14×4=56 行。

你在 SQL 里直接写上 from EMP, DEPT（两张表用逗号隔开，不写任何连接条件），MySQL 就会先给你算出这对"冤家"的全 56 行笛卡尔积：

select EMP.ename, EMP.sal, DEPT.dname
from EMP, DEPT;
-- 结果：56 行（14×4）。每条 EMP 都配上了 4 条 DEPT
-- +-------+---------+------------+
-- | ename | sal     | dname      |
-- +-------+---------+------------+
-- | SMITH | 800.00  | ACCOUNTING |   SMITH 连上了 部门10
-- | SMITH | 800.00  | RESEARCH   |   SMITH 又连上了 部门20
-- | SMITH | 800.00  | SALES      |   SMITH 又连上了 部门30
-- | SMITH | 800.00  | OPERATIONS |   SMITH 又连上了 部门40
-- | ALLEN | 1600.00 | ACCOUNTING |   ALLEN 开头……
-- | ...   | ...     | ...        |
-- +-------+---------+------------+

看到问题了吧。这个结果完全是"排列组合"，毫无意义——SMITH 一个人被复制成了 4 行，每行配一个不同的部门。这就是笛卡尔积的误用：只要你不给连接条件，结果就会爆炸式膨胀，而且全是错误数据。真实的业务里，两个大表这样一拼，几十上百万行瞬间就没了。

那我们想要的是什么呢？我们只要"EMP.deptno 等于 DEPT.deptno"的那些行。所以正确姿势是：先做出笛卡尔积，再用 WHERE 把无关的"跨部门组合"滤掉。这种"先积、再按条件过滤"的写法，就是 SQL 里最早的一种连接方式，叫**（等值）连接**，也叫等值连接或表连接。因为它是最经典的"多少年前的老写法"，在 1992 年之前是没有 JOIN 关键字的，全部靠这种"逗号加 WHERE"写，所以也被称为隐式连接或标准 SQL-92 之前的旧式写法。虽然现在更流行显式 JOIN，但旧写法在很多教材、老代码里依然大量存在，你必须能看懂它。

select EMP.ename, EMP.sal, DEPT.dname
from EMP, DEPT                              -- 先做笛卡尔积
where EMP.deptno = DEPT.deptno;             -- 再过滤：只留部门编号相等的组合
-- 结果：恰好 14 行（每个员工连上自己所属的部门）
-- +-------+---------+------------+
-- | ename | sal     | dname      |
-- +-------+---------+------------+
-- | SMITH | 800.00  | RESEARCH   |   SMITH 部门20 → RESEARCH✓
-- | ALLEN | 1600.00 | SALES      |   ALLEN 部门30 → SALES✓
-- | WARD  | 1250.00 | SALES      |
-- | JONES | 2975.00 | RESEARCH   |
-- | ...   | ...     | ...        |
-- | MILLER| 1300.00 | ACCOUNTING |
-- +-------+---------+------------+

董事长的数据出来了。14 名员工各自连上了自己所属的部门。这里我们还引入了表别名这个概念——其实严格说上面还没起别名，是直接用表名 EMP.、DEPT. 加一个点来"指名道姓"地引用列。为什么要费劲写 EMP.ename 而不是直接写 ename？因为 ename 这个字段名只有 EMP 有，本来不冲突；可 deptno 是 EMP 和 DEPT 两表都有的字段，如果直接写 where deptno = deptno，MySQL 会懵掉：你到底想比哪张表的 deptno？所以，凡是"两表同名"的列，以及其他任何可能产生歧义的地方，都推荐用"表名.列名"的方式写清楚。

表别名：让名字短一点，也让自连接成为可能

表别名（Table Alias）就是给表再起一个短小的临时名字。它有两种作用：一来省事，二来（最关键地）让"同一张表出现两次"成为可能——这是下一节自连接的基础。

select e.ename, e.sal, d.dname          -- e 是 EMP 的别名，d 是 DEPT 的别名
from EMP e, DEPT d                      -- 紧跟表名起的别名
where e.deptno = d.deptno;              -- 用别名来引用列，更短

效果和上面完全一样，只是把 EMP、DEPT 缩成了 e、d。当你的语句里有三张、四张表时，这种缩写能救你的命。注意别名的位置：在 from 的表名之后紧跟一个空格再写别名。

多表查询 + 条件筛选：部门号为 10 的员工

多表查询完全可以继续叠 WHERE 的其它条件。比如只显示部门 10 的部门名、员工名和工资：

select ename, sal, dname
from EMP, DEPT
where EMP.deptno = DEPT.deptno       -- 连接条件：先把两表连上
  and DEPT.deptno = 10;              -- 过滤条件：只要部门10
-- 结果：
-- +--------+---------+------------+
-- | ename  | sal     | dname      |
-- +--------+---------+------------+
-- | CLARK  | 2450.00 | ACCOUNTING |
-- | KING   | 5000.00 | ACCOUNTING |
-- | MILLER | 1300.00 | ACCOUNTING |
-- +--------+---------+------------+

这里 where 里混着两类条件：一句话概括就是"先连接、再筛选"。虽然从数学上讲这两步顺序可交换，但你先做连接条件、再做业务过滤条件，读起来思路最顺。

三表连查：加入 SALGRADE 求工资级别

多表查询不限于两张表。要显示"每个员工的姓名、工资及工资级别"，就需要 EMP、SALGRADE 两张表——用员工工资落在哪个级别区间来判断级别：

select e.ename, e.sal, s.grade          -- 员工名、工资、级别
from EMP e, SALGRADE s                  -- 两表做笛卡尔积
where e.sal between s.losal and s.hisal;-- 判断：工资落在该级别的上下限之间
-- 结果（节选）：
-- +--------+---------+-------+
-- | ename  | sal     | grade |
-- +--------+---------+-------+
-- | SMITH  | 800.00  |     1 |  800 在 700~1200 之间 → 1级
-- | JAMES  | 950.00  |     1 |
-- | ADAMS  | 1100.00 |     1 |
-- | MILLER | 1300.00 |     2 |  1300 在 1201~1400 → 2级
-- | WARD   | 1250.00 |     2 |
-- | MARTIN | 1250.00 |     2 |
-- | ...    | ...     | ...  |
-- | KING   | 5000.00 |     5 |  5000 在 3001~9999 → 5级
-- +--------+---------+-------+

between ... and ... 是"取闭区间"的意思，即 sal >= losal and sal <= hisal。注意这里的连接条件压根不用"两列相等"，而是用了一个区间判断——连接条件不一定是等号，它只是"两表行与行之间如何才算配对"的规则。这条原则后面还会用到。

到这里，多表查询的核心思想你已经掌握了：别忘了 WHERE 里的连接条件，否则就是一场笛卡尔积灾难。到了下一节，我们用一个更刁钻的场景——自连接，来检验你到底有没有真正吃透"表别名"。

自连接：让一张表和自己牵手

什么是自连接（Self Join）？一句话：在同一张表上做连接查询——把一张表"当成两张表"来用，然后用表别名把这两份"自己"区分开。

听起来很奇怪，为什么好好的一张表要自己连自己？因为有些信息，天然存在"表内的一对多关系"里。最典型的例子就是这里的 EMP 表：MGR 字段存的是"上级领导的 EMPNO"，而领导本人也是 EMP 表里的一员。也就是说，"谁是谁的领导"这种上一级关系，其实藏在一张表内部。

需求来了：显示员工 FORD 的上级领导的编号和姓名。FORD 的 MGR 是 7566，而 7566 正是 JONES 的 EMPNO。我们给出两种解法。

方法一：用子查询。先查出 FORD 的 MGR，再拿这个值去 EMP 里找人：

select empno, ename
from EMP
where EMP.empno = (select mgr       -- 外层:找编号等于下面子查询结果的那个人
                   from EMP
                   where ename='FORD');  -- 内层:先查出 FORD 的领导编号 (7566)
-- 结果：
-- +-------+-------+
-- | empno | ename |
-- +-------+-------+
-- |  7566 | JONES |   FORD 的领导是 7566 (JONES)
-- +-------+-------+

方法二：用自连接。我们把 EMP 表"复制"成两份，一份扮演"下属"，一份扮演"领导"，然后按 领导.empno = 下属.mgr 来配对：

select leader.empno, leader.ename   -- 从"领导"这张表里取编号和姓名
from EMP leader, EMP worker         -- leader 和 worker 都是 EMP，只是扮演不同角色
where leader.empno = worker.mgr     -- 配对规则：领导的编号 = 下属的 mgr
  and worker.ename = 'FORD';        -- 挑出下属是 FORD 的那一组
-- 结果（和方法一完全一致）：
-- +-------+-------+
-- | empno | ename |
-- +-------+-------+
-- |  7566 | JONES |
-- +-------+-------+

注意 from EMP leader, EMP worker 这里，我必须给前后两个 EMP 起不同的别名（leader 和 worker）。如果不写别名、写成 from EMP, EMP，MySQL 会直接报错——它根本分不清这两个表谁是哪个。别名在这里不只是"省事"，而是语法层面必须的区分手段。

自连接的执行逻辑是：先把 EMP 当成 leader、把 EMP 再当成 worker，做一次"两个自己"的满匹配（等价于笛卡尔积）；然后按 leader.empno = worker.mgr 过滤，就得到了"领导—下属"的对应关系；最后再限定 worker.ename='FORD'，就精确锁定了 FORD 的领导。

用自连接你能做一堆"层级"查询：查每个人的领导是谁、查每个领导带几名下属、甚至查"张三的领导的领导"。这也是面试里最高频的自连接考题。下面这行"查每个领导的手下人数"，用自连接一句话就能出来，留作你观察的素材：

select leader.empno, leader.ename, count(*) as 下属数
from EMP leader, EMP worker
where leader.empno = worker.mgr        -- 领导编号 = 下属的 mgr，配对出上下级
group by leader.empno, leader.ename;   -- 按 领导 分组，数下属

小结一下：自连接的核心就是"一台表，两个别名，各自分工"。它把"表内层级关系"这种抽象问题，硬生生翻译成了普通的两表连接——这正是别名的威力所在。

子查询：查询里的查询

子查询（Subquery），也叫嵌套查询，是指在一条 SQL 语句中，还嵌入了另一条 select 语句。这个内嵌的 select 先执行，把结果交给外层的语句使用。上一节我们其实已经悄悄用过几次子查询（比如 where sal = (select max(sal) from EMP)），只不过是在一张表上。复合查询里，子查询反而常常和多表查询组合出拳。

按照"子查询返回几行几列"，可以把子查询分成几类：单行子查询（返回一行一列）、多行子查询（返回多行，但只有一列）、多列子查询（返回多列）。我们一个个讲。

单行子查询

单行子查询是指"只返回一行记录"的子查询。它通常配 =、>、< 这些普通比较符使用，因为外部条件往往期望"拿一个值去比"。

案例：找出与 SMITH 在同一个部门的员工。SMITH 的部门是 20，所以先取出 SMITH 的 deptno，再用 = 去匹配：

select * from EMP
where deptno = (select deptno        -- 拿子查询算出的部门号去比
                from EMP
                where ename='SMITH'); -- 子查询返回 SMITH 的部门号，一行一列：20
-- 结果：部门20的全部员工（SMITH 自己也包含在内，因为 SMITH 部门确实是20）
-- +-------+-------+---------+------+------------+---------+------+--------+
-- | 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 | 1982-12-09 | 3000.00 | NULL |     20 |
-- |  7876 | ADAMS | CLERK   | 7788 | 1983-01-12 | 1100.00 | NULL |     20 |
-- |  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
-- +-------+-------+---------+------+------------+---------+------+--------+

单行子查询有一个著名陷阱：如果子查询返回了多行，那么 where deptno = (子查询) 里的 = 就会报错——一个值怎么能同时等于多个值？MySQL 会毫不客气地抛错（类似 "Subquery returns more than 1 row"）。这时候你就得改用下一节的多行运算符。

多行子查询：IN、ALL、ANY

多行子查询是指"返回多行记录，但通常只有一列"的子查询。这时候外部不能用普通的 =、> 去比（值对多行比较不了），而要改用专门针对"一组值"的运算符：IN、ALL、ANY（以及 NOT IN / NOT ANY 等变体）。

先认识 IN。where 字段 in (一组值) 的意思是"只要字段的值落在这组值里，就算匹配"，等价于多个 = 用 OR 拼起来。

案例：找出"岗位与 10 号部门里员工相同"的雇员，显示姓名、岗位、工资、部门号，但不包含 10 号部门自己的员工。

select ename, job, sal, deptno
from EMP
where job in (select distinct job    -- 子查询:列出10号部门的所有岗位,去重
              from EMP
              where deptno=10)
  and deptno <> 10;                  -- 排除掉10号部门自己
-- 10号部门的岗位: PRESIDENT(KING)、MANAGER(CLARK)、CLERK(MILLER)
-- 结果：凡是岗位落在上述三类的、且不是10号部门的员工
-- +-------+--------+---------+--------+
-- | ename | job    | sal     | deptno |
-- +-------+--------+---------+--------+
-- | JONES | MANAGER| 2975.00 |     20 |
-- | BLAKE | MANAGER| 2850.00 |     30 |
-- | SMITH | CLERK  |  800.00 |     20 |
-- | ADAMS | CLERK  | 1100.00 |     20 |
-- | JAMES | CLERK  |  950.00 |     30 |
-- | MILLER| ...    | ...     |        |
-- +-------+--------+---------+--------+

注意子查询里写了 distinct（去重）：job in (select job ...) 不去重其实结果也一样，但写上 distinct 能避免"一组值里有重复"导致的多余扫描，也是个好习惯。<> 表示"不等于"。

接下来是 ALL。sal > ALL(一组值) 表示"必须比这一组里的每一个都大"，通俗说就是"比它们全都大"。它等价于"大于这组里的最大值"。

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

select ename, sal, deptno
from EMP
where sal > all(select sal       -- 比30号部门每个员工工资都高 → 大于其最大值
                from EMP
                where deptno=30);  -- 30号部门工资分别是 1600,1250,1250,2850,1500,950
-- 30号部门最高工资是 BLAKE 的 2850，所以 sal>2850 即可
-- 结果：
-- +-------+---------+--------+
-- | ename | sal     | deptno |
-- +-------+---------+--------+
-- | JONES | 2975.00 |     20 |
-- | SCOTT | 3000.00 |     20 |
-- | KING  | 5000.00 |     10 |
-- | FORD  | 3000.00 |     20 |
-- +-------+---------+--------+

再看 ANY。sal > ANY(一组值) 表示"只要比这一组里的某一个大就可以"，通俗说就是"比它们中最小的那个大就行"。它等价于"大于这组里的最小值"。

案例：显示工资比部门 30 的任意一个员工工资高的员工的姓名、工资和部门号。

select ename, sal, deptno
from EMP
where sal > any(select sal        -- 比30号部门任意一人高 → 大于其最小值
                from EMP
                where deptno=30);  -- 30号部门最低工资是 JAMES 的 950
-- 结果：所有工资 >950 的员工（SMITH=800、JAMES=950 被排除）
-- +--------+---------+--------+
-- | 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 |
-- | MILLER | 1300.00 |     10 |
-- +--------+---------+--------+

把 ALL 和 ANY 的记忆锚点刻进脑子：

ALL：对一组所有值都成立 → 相当于取"最大/最小"那一端（> 配 max，< 配 min）。
ANY：对一组值有一个成立就行 → 相当于取"最宽松"那一端（> 配 min，< 配 max）。

一句话记："ALL"是逼着你通吃，苛刻；"ANY"是只要中一个，宽容。所以 sal > ALL 门槛最高（要赢过所有人），sal > ANY 门槛最低（只要赢过最菜的）。

IN 与 EXISTS：各有所长的孪生兄弟

说到 IN，就不得不提它的好兄弟 EXISTS。两者都能干"子查询里有没有匹配"这类活儿，但底层思路完全不同，这直接决定了它们各自的适用场景，也是面试官最爱挖的坑。

IN：先执行子查询，把子查询返回的"一组值"完整算出来，然后外层拿每一行去这组值里做匹配。它关心的是"值是否在集合里"。
EXISTS：对外层的结果逐行"带入"子查询（关联子查询），只要子查询"查得出哪怕一行"，就认为该行满足条件。它关心的是"存不存在"，一旦命中就立刻返回，不继续往下找。

EXISTS 最常见的写法是配合"外层表某列与子查询里某列相关联"的关联子查询：

select ename
from EMP e
where exists (select 1              -- exists 只关心有没有行，写 * 或 1 都行
              from DEPT d
              where d.deptno = e.deptno   -- 关联:子查询用到外层的 e.deptno
                and d.dname = 'RESEARCH'); -- 部门名字是 RESEARCH
-- 结果：所有在部门20(RESEARCH)的员工
-- +-------+
-- | ename |
-- +-------+
-- | SMITH |
-- | JONES |
-- | SCOTT |
-- | ADAMS |
-- | FORD  |
-- +-------+

exists 子查询里 select 1 是因为它根本不在乎选什么列，只在乎"有没有行"，写 1 比写 * 更省心也更高效一点点。

**什么时候用哪个？**经验法则：子查询结果集很小、且外层表很大时，IN 常常更顺手（它把子查询算一次，再去外层大表里走索引匹配）；当子查询结果集可能很大、但外层表相对较小时，EXISTS 常见更优（它按外层行逐个探测，命中即停，还可以用上合适的索引）。不过这些都是经验之谈，现代数据库优化器往往会把你写的 IN、EXISTS 重写成同一种高效执行计划，所以入门阶段，优先保证语义正确，不必过度纠结性能差异。

这里埋一个本节最重要的坑——IN 遇到 NULL：如果子查询返回的那组值包含 NULL，那么 not in (....) 会直接返回"查询不到任何行"，哪怕明明有匹配的行。这背后的逻辑很微妙：not in 等价于"不等于每一个值"，而任何一个值 = NULL 的结果都是"未知"(NULL)，数据库对"所有比较结果都是未知"的行会直接丢弃。所以真实的业务里，一旦你发现"not in 怎么一条数据都查不出来"，第一反应就应该是：子查询结果里有没有 NULL？

多列子查询

前面的子查询无论是单行还是多行，本质上都是单列的。而多列子查询，则是指"子查询一次返回多个列的数值"。它通常配合"元组比较"使用——把多个列打包成一个整体（一个元组），用一个比较符整体去比较。

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

select ename from EMP
where (deptno, job) = (select deptno, job    -- 左侧是"两列打包"的元组
                       from EMP
                       where ename='SMITH')  -- 右侧一次返回 SMITH 的(部门,岗位)= (20, CLERK)
  and ename <> 'SMITH';                      -- 排除 SMITH 自己
-- 结果：
-- +-------+
-- | ename |
-- +-------+
-- | ADAMS |   ADAMS 也是部门20、CLERK，且不是 SMITH
-- +-------+

语法上，(deptno, job) 这个写在括号里、用逗号隔开的"列的组合"，就是一个元组（tuple，把几个字段捆成一个整体）。(deptno, job) = (20, 'CLERK') 的含义是"两列同时分别相等"，即 deptno = 20 AND job = 'CLERK'。它是把一个 "AND 关系" 的复合条件，用元组语法一次性写了出来——比 where deptno=... and job=... 更紧凑，也更清晰地表达"整组条件必须同时成立"的意图。字典里 SMITH 的部门和岗位是 (20, CLERK)，于是全表里部门和岗位同时与它相同的只有 ADAMS。

从 FROM 子句看子查询：把查询当临时表

前面你见到的子查询都出现在 WHERE 里。但子查询还有一个更"进阶"的用法：出现在 FROM 子句中。此时，这个子查询的结果会被当作一张"临时表"（derived table，派生表）来用——你可以先把一步统计在子查询里算好，然后像对待普通表一样，去连接它或者从它里面选东西。这正是"把复杂问题拆成两步"的分治思想。

案例一：显示每个高于自己部门平均工资的员工——姓名、部门、工资、平均工资。

思路一句话：先算出每个部门的平均工资，做成一张"部门→平均工资"的临时表；再把 EMP 和这张临时表按部门号连接，最后过滤出"工资 > 本部门平均工资"的员工。

select e.ename, e.deptno, e.sal,            -- 员工本人信息
       format(tmp.asal, 2)                   -- 他所在部门的平均工资
from EMP e,
     (select avg(sal) asal, deptno dt        -- 子查询:每个部门的平均工资
      from EMP
      group by deptno) tmp                   -- 把子查询结果当成别名为 tmp 的临时表
where e.sal > tmp.asal                       -- 过滤:工资高于本部门平均
  and e.deptno = tmp.dt;                     -- 连接条件:部门编号对上
-- 结果（节选部门20、30）：
-- 部门20平均 2175，高于它的：JONES(2975)、SCOTT(3000)、FORD(3000)
-- 部门30平均 1566.67，高于它的：ALLEN(1600)、BLAKE(2850)
-- +-------+--------+---------+--------------+
-- | ename | deptno | sal     | format(...)  |
-- +-------+--------+---------+--------------+
-- | JONES |     20 | 2975.00 | 2175.00      |
-- | SCOTT |     20 | 3000.00 | 2175.00      |
-- | ...   |  ...   |  ...    | ...          |
-- +-------+--------+---------+--------------+

看到 tmp.dt 和 tmp.asal 了吗。在子查询里我给 avg(sal) 起了别名 asal、给 deptno 起了别名 dt，这样外层用 tmp.asal、tmp.dt 就能精确引用到临时表里的这两列。子查询出来的临时表必须有个名字才能被外层 from EMP, (...) tmp 引用——这个 tmp 就是它的表别名。

案例二：查找每个部门工资最高的人的姓名、工资、部门，以及该部门最高工资。

思路：先在子查询里算"每个部门的最高工资"，再拿 EMP 去连接，条件是 EMP.deptno = 临时表.deptno 且 EMP.sal = 临时表.ms。

select e.ename, e.sal, e.deptno, tmp.ms      -- 员工信息和部门最高工资
from EMP e,
     (select max(sal) ms, deptno              -- 子查询:每部门最高工资
      from EMP
      group by deptno) tmp                    -- 别名为 tmp
where e.deptno = tmp.deptno                   -- 连接:部门对上
  and e.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 | 部门20最高3000，SCOTT和FORD并列
-- | BLAKE | 2850.00 |     30 | 2850.00 |
-- +-------+---------+--------+---------+

注意到一个细节：部门 20 的最高工资 3000 是 SCOTT 和 FORD 并列的，所以"工资=部门最高"同时命中了两个人。这说明"最高工资的人"未必只有一个人——这种并列情况，只用一条 where sal = max 是不行的（聚合函数不能用在 WHERE），必须先子查询算出 max 再比较，就绕开了这个限制。

案例三：显示每个部门的信息（部门名、编号、地址）和人员数量。这里给出两种方法，让你直观对比"多表直接连接分组"和"子查询当临时表"两条路线。

方法一：直接对 EMP、DEPT 两表连接后分组数人数。

select DEPT.dname, DEPT.deptno, DEPT.loc,
       count(*) as '部门人数'         -- 分组后数每个部门人数
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 |
-- +------------+--------+----------+----------+

这里有个必须讲清楚的坑：group by 后面我把 DEPT.deptno, DEPT.dname, DEPT.loc 三个列全部列了出来。为什么？当数据库开启了 ONLY_FULL_GROUP_BY 模式（MySQL 5.7 以后默认开启）时，select 中凡是"非聚合的列"（即不是 count/avg 等聚合函数结果的列），必须全部出现在 group by 里，否则就报错。因为 dname、loc 都只是"跟随 deptno"的附属信息，不把它们加进分组，数据库不知道该取哪行来代表这个组。这算是一条"顺手就踩"的规矩，尤其写这种"连表+分组+带上附属列"的查询时。

方法二：先用子查询把"每个部门的人数"算成一张临时表，再和 DEPT 连接。

-- 第1步：先单独统计每个部门的人数
select count(*) mycnt, deptno
from EMP
group by deptno;
-- 结果：
-- +-------+--------+
-- | mycnt | deptno |
-- +-------+--------+
-- |     3 |     10 |
-- |     5 |     20 |
-- |     6 |     30 |
-- +-------+--------+

-- 第2步：把上面的结果当成临时表 tmp，再和 DEPT 连接，补上部门名和地址
select d.deptno, d.dname, tmp.mycnt, d.loc
from DEPT d,
     (select count(*) mycnt, deptno
      from EMP
      group by deptno) tmp               -- 临时表:部门→人数
where d.deptno = tmp.deptno;             -- 连接:对上部门号
-- 结果：
-- +--------+------------+-------+----------+
-- | deptno | dname      | mycnt | loc      |
-- +--------+------------+-------+----------+
-- |     10 | ACCOUNTING |     3 | NEW YORK |
-- |     20 | RESEARCH   |     5 | DALLAS   |
-- |     30 | SALES      |     6 | CHICAGO  |
-- +--------+------------+-------+----------+

方法二把"统计人数"和"连部门名"两步拆分，思路更清晰，尤其是在统计逻辑复杂的时候，先把临时表做对，再讲连接，不易出错。两种写法结果一致，都是"分治"思想的不同体现。

在这一节末尾，我们把子查询和连接放在一起做个对比，这也是一处高频考点：

对比维度	子查询	连接（多表查询）
思维方式	分步：先算子集结果，再交给外层	一次性把多表拼起来再整体过滤
阅读难度	嵌套层级深时易乱	平铺直叙，直观
表达力	适合"算一个量再比较"（如最高/平均）	适合"跨表关联数据"
改写关系	很多时候两者可以互相改写	很多时候也能用子查询替代
性能	结果集大或关联复杂时可能慢	靠索引通常更高效，但有笛卡尔积爆炸风险

一句话：**能用连接明明白白说清的就优先连接；需要"先算一个中间量再基于它判断"的，用子查询更自然。**两者不是互斥的，实战里经常是"子查询来做临时表，外接连接"的混合体，案例三就是最好的例子。

合并查询：UNION 与 UNION ALL

复合查询的最后一块拼图是合并查询。它的目标不是一个表拼另一张表，而是：把两个（或多个）select 的结果集，上下堆叠成一个结果集。用到的操作符是集合操作里的"并集"，也就是 UNION 和 UNION ALL。

先理解它和"连接"的本质区别。连接是把两行横向拼成一行的组合（列变多）；**合并（UNION）**是把两组行纵向堆成一叠结果（行变多）。

UNION 和 UNION ALL 只差一件事：去不去重。

UNION（并集，自动去重）：取两个结果集的并集，自动去掉重复行。
UNION ALL（并集，不去重）：取两个结果集的并集，保留所有重复行。

案例：找"工资大于 2500 的人"和"岗位是 MANAGER 的人"两者的并集。

先用 UNION（自动去重）：

select ename, sal, job from EMP where sal > 2500
union                                          -- 去重合并
select ename, sal, job from EMP where job = 'MANAGER';
-- 结果（6行）：JONES、BLAKE 同时命中"工资>2500"和"MANAGER"两个集合，去重后只出现一次
-- +-------+---------+-----------+
-- | 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   |
-- +-------+---------+-----------+

再换成 UNION ALL（不去重）：

select ename, sal, job from EMP where sal > 2500
union all                                      -- 不去重合并
select ename, sal, job from EMP where job = 'MANAGER';
-- 结果（8行）：JONES、BLAKE 在两个集合都出现，各重复一次，共出现两次
-- +-------+---------+-----------+
-- | ename | sal     | job       |
-- +-------+---------+-----------+
-- | JONES | 2975.00 | MANAGER   |   ← 工资>2500 命中
-- | BLAKE | 2850.00 | MANAGER   |   ← 工资>2500 命中
-- | SCOTT | 3000.00 | ANALYST   |   ← 工资>2500 命中
-- | KING  | 5000.00 | PRESIDENT |   ← 工资>2500 命中
-- | FORD  | 3000.00 | ANALYST   |   ← 工资>2500 命中
-- | JONES | 2975.00 | MANAGER   |   ← MANAGER 命中（第二次出现）
-- | BLAKE | 2850.00 | MANAGER   |   ← MANAGER 命中（第二次出现）
-- | CLARK | 2450.00 | MANAGER   |   ← 仅 MANAGER 命中
-- +-------+---------+-----------+

对比一眼就懂了：UNION 去重后 6 行，UNION ALL 不去重是 8 行。多出来的 2 行，正是既满足"工资>2500"又满足"岗位是MANAGER"的 JONES 和 BLAKE。

用 UNION / UNION ALL 有几个硬性规矩，踩了直接报错：

两个结果集的列数必须一致。第一个 select 有几列，第二个就必须有几列，否则报"列数不匹配"。
对应位置的列类型要兼容。虽然 MySQL 会做隐式转换，但为了可读性和安全性，最好保持对应列类型一致。
UNION 的排序要注意：如果需要排序，可以在末尾统一 order by；但要注意，order by 作用于合并后的整体结果，不是各自的结果。

最后是一个性能与语义的取舍：因为 UNION 要去重，MySQL 内部必须对所有行做一次比较排序才能找出重复，代价较高；而 UNION ALL 只是简单地"接龙"堆叠，不去重、不排序，快得多。所以，如果你确定两个结果集本来就不可能有重复（或你根本不在乎重复），应该用 UNION ALL 而不是 UNION——这既省时间，也避免"想去重反而误杀了本来就不该去的行"这种语义风险。这是生产环境里经常被忽略的优化点。

子查询与连接的抉择：再深入一层

前面我们对比过子查询和连接的差异，这里我们再补两个实战频率极高的"抉择题"，帮你在真正的业务里少走弯路。

**第一题：什么时候该写"自连接"而不是"子查询"？**在很多"员工—领导""分类—子分类"这类表层级场景里，两者都能做。上一节的 FORD 领导问题就是证明。一般经验是：如果只要"查一层/查几个值"，子查询更直观；如果要"逐层统计、横向展示所有层级关系"，自连接更顺手。但两者的执行计划和性能取决于数据和索引，所以"能用、又好维护"就是你的第一原则。

**第二题：为什么有时候多表连接能做的，却偏要用 FROM 子查询？**因为某些"先聚合、再连接"的场景，直接连表没法写：你没法在 WHERE 里用聚合结果，也没法在普通连接里把"每部门的 max/avg"轻松带出来。于是把聚合放进 FROM 子查询变成临时表，外部再连接，就能优雅地表达"先算每组的统计量，再基于它过滤"。这是把"两步思考"翻译成 SQL 的标准套路。

实战演练：来两道 OJ 级题目练手

光看不练假把式。我们直接拿两道牛客（牛客网的 SQL 题库里同类型的经典题）风格的题目来实战，模拟真实考查你会不会把复合查询灵活组合起来。

实战题一：查找所有员工入职时候的薪水情况，给出 emp_no 以及 salary，并按照 emp_no 进行逆序排列。

这类考的是"同一张表的两份有用信息——谁、何时入职、入职当月薪水"。为了聚焦复合查询而不被数据建模分散注意力，我们把它简化成：给出所有员工的编号（EMPNO）和入职月工资，按编号倒序。

select EMPNO as emp_no,      -- 员工编号
       SAL    as salary      -- 工资
from EMP
order by EMPNO desc;         -- 按编号倒序
-- 结果（前几行）：
-- +--------+---------+
-- | emp_no | salary  |
-- +--------+---------+
-- |   7934 | 1300.00 |
-- |   7902 | 3000.00 |
-- |   7900 |  950.00 |
-- |   7876 | 1100.00 |
-- |   7844 | 1500.00 |
-- +--------+---------+

这是最简单的"单表 + 排序"垫场题。真正的难点在下面这道。

实战题二：获取所有非 manager 的员工 emp_no。

题目意思是：EMP 表里"当领导的人"（即出现在别人的 MGR 字段里的那个人），就是 manager；我们要的是"从来没当过领导"的普通员工。怎么表达"某个人不是领导"？核心是：一个人的编号，没有出现在任何人的 MGR 里。

方法一：用 NOT IN + 子查询。先找出所有"被人当作领导"的编号（即去重的 MGR），再排除掉这些编号：

select EMPNO as emp_no
from EMP
where EMPNO not in (select distinct MGR   -- 把所有领导编号查出来（排除NULL）
                    from EMP
                    where MGR is not null);  -- 关键:先过滤掉 MGR 的 NULL！
order by EMPNO;
-- 结果：
-- +--------+
-- | emp_no |
-- +--------+
-- |   7369 |  SMITH（从没带过下属）
-- |   7499 |
-- |   7521 |
-- |   7654 |
-- |   7844 |
-- |   7876 |
-- |   7900 |
-- |   7934 |
-- +--------+

这里我特意在子查询里写了 where MGR is not null。为什么？还记得前面埋的"NOT IN 遇到 NULL 就全军覆没"的坑吗？KING 的 MGR 是 NULL（他上面没人），如果不去掉 NULL，EMPNO NOT IN (……, NULL) 会直接一个都查不出来。所以任何"NOT IN"姿势，都先检查子查询结果里有没有 NULL——这是这道题真正的考点，也是生产环境里最容易踩的雷。

方法二（更稳健）：用 NOT EXISTS 关联子查询。对每个员工，检查"是否存在某条记录，它的 MGR 等于我的 EMPNO"，不存在则为非领导。因为 NOT EXISTS 天然不踩 NULL 的坑，往往更安全：

select e.EMPNO as emp_no
from EMP e
where not exists (select 1              -- 是否存在“以我当领导”的记录
                  from EMP m
                  where m.MGR = e.EMPNO); -- 关联:某人的领导恰好是我
order by e.EMPNO;
-- 结果与上面 NOT IN 完全一致（8名非领导的普通员工）

对比可见：NOT IN 写起来简单，但你必须记得处理 NULL；NOT EXISTS 语义更"防水"，通常也更推荐。这道题同时把 IN/EXISTS、子查询、以及 NULL 陷阱全串起来了，是检验你今天学习成果的绝佳题目。

学以致用：自测题与详解

到这里，复合查询的三大件——多表查询、子查询、合并查询——就全部讲完了。下面给你出 6 道自测题，先自己动手敲、再对下面的详解答案。别偷看答案，这 6 道全过了，你的复合查询就算真正焊进脑子里了。

自测 1（多表 + 笛卡尔积）：select ename, dname from EMP, DEPT; 会返回多少行？为什么？如果这是一段生产代码，可能错在哪？

自测 2（三表连查）：请写一条 SQL，显示每个员工的姓名、部门名、工资级别，三表（EMP、DEPT、SALGRADE）都用上。

自测 3（子查询 + ALL/ANY）：写一条 SQL，找出"所在部门不是 30，但工资比 30 号部门所有人的工资都高"的员工，并说明为什么用 ALL 而不是 ANY。

自测 4（自连接）：用自连接写一条 SQL，找出"FORD 的领导的领导"的编号和姓名，并说明这条语句在干什么。

自测 5（合并查询）：select ename from EMP where job='CLERK' union select ename from EMP where deptno=20; 返回几个人的名字？如果改成 union all 呢？去重的原因是什么？

自测 6（NULL 陷阱）：select ename from EMP where deptno not in (select deptno from EMP where deptno = 10 or deptno is null); 在咱们这份数据下会返回什么？再进一步想：假设数据里真有某行 EMP.deptno 为 NULL（注意咱这份数据的 NULL 只出现在 KING 的 MGR 这一列上，deptno 并没有 NULL），整条语句会不会出问题？请给出"无论子查询里有没有 NULL 都稳健"的写法，并说明其中的关键判断。

下面是详解答案：

自测 1 答案：EMP 有 14 行，DEPT 有 4 行，from EMP, DEPT 没写连接条件，做的是完整笛卡尔积，所以返回 14×4=56 行。每名员工都被复制成 4 行、分别配上一个无关的部门。如果这是生产代码，很可能错在：忘了加 where EMP.deptno = DEPT.deptno 的连接条件，导致结果行数虚高、数据全部串位。笛卡尔积在"故意"的场景下（比如求所有组合）有用，但绝大多数时候是事故现场。修复方法就是补上连接条件。

自测 2 答案：

select e.ename, d.dname, s.grade
from EMP e, DEPT d, SALGRADE s
where e.deptno = d.deptno        -- 员工↔部门 连接
  and e.sal between s.losal and s.hisal;  -- 员工↔工资级别 连接（区间判断）

三张表做笛卡尔积（14×4×5=280 行），然后用两个连接条件逐步过滤：先按部门号对上 DEPT，再按工资落在某级别的上下限之间对上 SALGRADE。每名员工最终落在他所属部门 + 他工资对应的级别上。连接条件不一定是等号，between 照样是合法的连接规则。

自测 3 答案：

select ename, sal, deptno
from EMP
where deptno <> 30                                          -- 排除30号部门
  and sal > all(select sal from EMP where deptno = 30);     -- 比30号部门所有工资都高

用 ALL 是想表达"比 30 号部门的每一个人都高"，这是最苛刻的要求，等价于"高于 30 号部门的最高工资（2850）"。若用 ANY，只要求"高于其中任意一个"，等价于"高于其最低工资（950）"，条件立刻放得很宽，非 30 号部门里大量工资几千的人都满足，就达不到"比他们全都高"的意图了。所以语义要"通吃"就用 ALL。

自测 4 答案：

select ll.empno, ll.ename              -- 太上级：领导的领导
from EMP ll, EMP l, EMP w              -- 三份 EMP：太上级、直接领导、本人
where ll.empno = l.mgr                 -- 太上级 是 直接领导 的领导
  and l.mgr = w.empno                  -- 直接领导 是 本人 的领导
  and w.ename = 'FORD';                -- 限定本人是 FORD
-- 结果：FORD 的直接领导是 JONES(7566)，JONES 的领导是 KING(7839)
-- +-------+-------+
-- | empno | ename |
-- +-------+-------+
-- |  7839 | KING  |
-- +-------+-------+

这是自连接"一层套一层"的延展：把 EMP 拆成三份，分别扮演太上级、直接领导、本人，用两条等值连接把链条串起来。FORD → JONES → KING，向上两级就是 KING。想查"向上 N 级"，本质上就是在自连接里多"复制"一份 EMP、多写一条连接条件。

自测 5 答案：第一个集合是"CLERK"岗的 4 人：SMITH、ADAMS、JAMES、MILLER；第二个集合是"部门 20"的 5 人：SMITH、JONES、SCOTT、ADAMS、FORD。两集合的并集共 7 个不同的人，所以 union 返回 7 行（SMITH、ADAMS 在两边都出现，去重后只留一次）。改成 union all，两个重复的人各多留一份，返回 7+2=9 行。去重的原因：union 自动把两个结果集的交集（同时属于两集合的 SMITH、ADAMS）合并成一份，只保留一次。

自测 6 答案：先分清两件事——在咱们这份数据下，所有员工的 deptno 都没有 NULL（NULL 只在 KING 的 MGR 这一列），所以子查询 select deptno from EMP where deptno = 10 or deptno is null 只会返回 {10}。于是 deptno not in (10) 能正常查出全部 11 名非 10 号部门的员工：SMITH、ALLEN、WARD、JONES、MARTIN、BLAKE、SCOTT、TURNER、ADAMS、JAMES、FORD（即除 CLARK、KING、MILLER 之外的所有人）。

但这条语句真正的风险藏在"假设"里：如果数据里真的存在某行 EMP.deptno 为 NULL，那么 deptno = 10 or deptno is null 就会把那个 NULL 也选进子查询结果，此时 not in (10, NULL) 会一条都查不出来——因为任何行和 NULL 做"等于/不等于"比较，结果永远是"未知"，数据库把所有行都当成"不满足条件"丢掉了。这正是 NOT IN 遇 NULL 就全军覆没的经典陷阱。

所以"无论子查询里有没有 NULL 都稳健"的正确写法，是先在子查询里排除掉 NULL，同时外层也显式把 deptno 为空的员工过滤掉（没有部门的员工本就不该参与"非 10 号部门"的统计）：

select ename from EMP
where deptno not in (select deptno from EMP
                     where deptno = 10)   -- 子查询只取10号部门的 deptno，本身就是10，不会带进NULL
  and deptno is not null;                 -- 外层再显式排除 deptno 为空的员工

当然，最稳妥、也最能体现你今天所学的是直接避开 NOT IN，改用 NOT EXISTS 关联子查询——它天生不受 NULL 干扰。这也再次验证了全文反复强调的结论：凡用到 NOT IN，先检查子查询里有没有 NULL；能用 NOT EXISTS 就用 NOT EXISTS。

收尾

从一张表起步，我们一步步把查询的边界推了出去。先是多表查询：理解了笛卡尔积这张"先积后滤"的底牌，学会了用连接条件把 from EMP, DEPT 的 56 行收敛成真正有意义的 14 行，也认清了忘写连接条件会把数据拼成灾难现场。接着是自连接：一张表用两个别名，把"领导是谁"这种表内层级关系翻译成了普通的两表连接。然后是子查询：单行的 =、多行的 IN/ALL/ANY、多列的元组比较、以及藏在 FROM 里的"临时表"技法，还厘清了 IN 与 EXISTS 这对孪生兄弟的性格差异，以及它们各自踩 NULL 的脾气。最后是合并查询：UNION 自动去重、UNION ALL 原样堆叠，一句话记就是"想省事且确定无重复，就上 UNION ALL"。

这几样东西从来不是孤立存在的。真实的题，永远是"先 WHERE 过滤、再分组聚合、必要的时候上子查询或连表、最后合并"的组合拳。你把这套"组合"的思路刻进脑子，再配合大量动手敲，就再也不会被"查一条数据要三张表"这种事吓住了。

整篇文章里所有例子，都希望你亲手执行一遍；被报错折磨过、被结果印证过的知识点，才是真正属于你的。下一篇文章，我们会进入 MySQL 更进阶的领域——索引与性能优化，去看看"同样的查询，为什么有的走索引快如闪电，有的却全表扫描慢如蜗牛"。到那时候，你今天学的这些查询，会变成验证性能优化的最佳实验品。准备好了吗？