MySQL 复合查询全解析：从单表到多表的实战进阶

1. 单表查询回顾：夯实基础操作

1.1 多条件筛选查询

1.2 自定义排序查询

1.3 聚合与筛选结合查询

2. 多表查询：关联多张表取数

2.1 两表关联查询

2.2 三表关联查询

3. 自连接：同一张表的 “自我关联”

4. 子查询：嵌套查询的灵活应用

4.1 单行子查询（返回 1 行结果）

4.2 多行子查询（返回多行结果）

4.3 子查询嵌入 from 子句

5. 合并查询：union 与 union all

在 MySQL 日常使用中，单表查询仅能满足基础数据需求，而实际开发中，数据往往分散在多张表中，且需要复杂的条件筛选与统计。本文将从单表查询回顾入手，逐步深入多表查询、自连接、子查询等复合场景，结合真实案例拆解用法，帮你掌握企业级查询技巧。

1. 单表查询回顾：夯实基础操作

单表查询是复合查询的基石，**核心围绕「筛选条件」「排序规则」「聚合统计」**三大维度展开，以下通过经典案例复习关键用法。

1.1 多条件筛选查询

需求：查询工资高于 3000 或岗位为「ANALYST」的雇员，且姓名首字母为大写「S」。实现方式有两种：通配符匹配或字符串截取函数，结果一致但适用场景不同。

方式 1：使用like通配符（更简洁，适合模糊匹配场景）

1select * from emp 
2where (sal > 3000 or job = 'ANALYST') 
3  and ename like 'S%';
4

方式 2：使用**substring**函数（更精准，适合固定位置匹配场景）

1select * from emp 
2where (sal > 3000 or job = 'ANALYST') 
3  and substring(ename, 1, 1) = 'S';
4

查询结果（示例）：

1+--------+-------+---------+------+---------------------+---------+------+--------+
2| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
3+--------+-------+---------+------+---------------------+---------+------+--------+
4| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
5+--------+-------+---------+------+---------------------+---------+------+--------+
61 row in set (0.00 sec)
7

1.2 自定义排序查询

排序不仅支持表中原有字段，还能基于「计算字段」排序，比如按「年薪」排序。需注意：奖金comm可能为NULL，需用ifnull函数处理空值，避免计算结果异常。

场景 1：按「月薪 ×12」计算年薪排序

1select ename, sal*12 as 年薪 
2from emp 
3order by 年薪 desc;
4

场景 2：按「月薪 ×12 + 奖金」计算年薪排序（处理空值）

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

1.3 聚合与筛选结合查询

聚合查询（avg/max/count等）需搭配group by分组，若需过滤聚合结果，需用having（区别于where过滤行数据）。

场景 1：查询每个部门的平均工资和最高工资

1select deptno, avg(sal) as 平均工资, max(sal) as 最高工资 
2from emp 
3group by deptno;
4

场景 2：查询平均工资低于 2500 的部门（聚合后筛选）

1select deptno, avg(sal) as 平均工资 
2from emp 
3group by deptno 
4having 平均工资 < 2500;
5

查询结果（示例）：

1+--------+-------------+
2| deptno | 平均工资    |
3+--------+-------------+
4|     20 | 2175.000000 |
5|     30 | 1566.666667 |
6+--------+-------------+
72 rows in set (0.00 sec)
8

2. 多表查询：关联多张表取数

实际开发中，数据常分散在多张表（如员工表emp、部门表dept、工资等级表salgrade），需通过「关联字段」（如deptno）将表连接，获取完整信息。

2.1 两表关联查询

核心逻辑：找到两张表的共同字段（关联字段），用where指定关联条件，避免笛卡尔积（数据重复）。

需求 1：显示雇员名、工资及所在部门名称（关联emp和dept）

1select e.ename, e.sal, d.dname 
2from emp e, dept d  -- 给表起别名，简化代码
3where e.deptno = d.deptno;  -- 关联条件：员工表部门号=部门表部门号
4

需求 2：显示 10 号部门的部门名、员工名和工资（关联 + 筛选）

1select d.dname, e.ename, e.sal 
2from emp e, dept d 
3where e.deptno = d.deptno 
4  and e.deptno = 10;  -- 额外筛选10号部门
5

2.2 三表关联查询

当需要从三张表取数时，需依次指定表间关联条件，确保数据逻辑正确。

需求：显示每个员工的姓名、工资、部门名称及工资等级（关联emp/dept/salgrade）

1select e.ename, e.sal, d.dname, s.grade 
2from emp e, dept d, salgrade s 
3where e.deptno = d.deptno  -- 关联emp和dept
4  and e.sal between s.losal and s.hisal;  -- 关联emp和salgrade（工资在等级范围内）
5

3. 自连接：同一张表的 “自我关联”

自连接是特殊的多表查询，指同一张表通过别名视为两张表，解决 “表内数据关联” 场景（如查询员工的上级领导）。

需求：显示员工「FORD」的上级领导编号和姓名（emp表中mgr字段是领导的empno）

1select leader.empno as 领导编号, leader.ename as 领导姓名 
2from emp emp, emp leader  -- 同一张表起两个别名：员工表(emp)、领导表(leader)
3where emp.ename = 'FORD'  -- 筛选员工FORD
4  and emp.mgr = leader.empno;  -- 关联条件：员工的领导编号=领导的员工编号
5

查询结果（示例）：

1+--------+-----------+
2| 领导编号 | 领导姓名  |
3+--------+-----------+
4|  007566 | JONES     |
5+--------+-----------+
61 row in set (0.00 sec)
7

4. 子查询：嵌套查询的灵活应用

子查询（嵌套查询）指将一个select语句嵌入到另一个 SQL 语句中，按返回结果行数可分为「单行子查询」「多行子查询」，按位置可嵌入where或from子句。

4.1 单行子查询（返回 1 行结果）

适用于 “基于单个值筛选” 的场景，常用=匹配子查询结果。

需求：查询与「SMITH」同一部门的所有员工（不含 SMITH）

1select * from emp 
2where deptno = (select deptno from emp where ename = 'SMITH')  -- 子查询：获取SMITH的部门号
3  and ename != 'SMITH';  -- 排除SMITH本人
4

4.2 多行子查询（返回多行结果）

适用于 “基于多个值筛选” 的场景，需搭配in/all/any等关键字。

关键字	作用说明
in	匹配子查询结果中的任意一个值
all	匹配所有子查询结果（如> all表示大于所有值）
any	匹配任意一个子查询结果（如> any表示大于其中一个值）

场景 1：用in查询与 10 号部门岗位相同的员工（不含 10 号部门）

1select ename, job, sal, deptno 
2from emp 
3where job in (select distinct job from emp where deptno = 10)  -- 子查询：10号部门的所有岗位
4  and deptno != 10;  -- 排除10号部门
5

场景 2：用all查询工资高于 30 号部门所有员工的员工

1select ename, sal, deptno 
2from emp 
3where sal > all(select sal from emp where deptno = 30);  -- 大于30号部门所有工资
4

4.3 子查询嵌入 from 子句

将子查询结果视为「临时表」，用于复杂统计（如查询 “高于部门平均工资的员工”）。

需求：显示每个高于自己部门平均工资的员工姓名、部门、工资及部门平均工资

1select e.ename, e.deptno, e.sal, format(tmp.部门平均工资, 2)  -- format格式化小数
2from emp e, 
3     (select deptno, avg(sal) as 部门平均工资 from emp group by deptno) tmp  -- 子查询作为临时表tmp
4where e.deptno = tmp.deptno  -- 关联员工表和临时表
5  and e.sal > tmp.部门平均工资;  -- 筛选高于平均工资的员工
6

5. 合并查询：union 与 union all

当需要合并多个select的结果集时，可使用union或union all，两者核心区别是是否去重。

操作符	去重情况	性能	适用场景
union	自动去重	较低（需比对去重）	需避免结果重复
union all	不去重	较高（直接合并）	结果无重复或允许重复

需求：查询工资高于 4000 或岗位为「PRESIDENT」的员工

1-- union去重（若有重复数据会自动剔除）
2select * from emp where sal > 4000 
3union 
4select * from emp where job = 'PRESIDENT';
5
6-- union all不去重（性能更优，适合确认无重复的场景）
7select * from emp where sal > 4000 
8union all 
9select * from emp where job = 'PRESIDENT';
10

5. 表的连接：内连接与外连接详解

在多表查询中，表的连接方式直接决定了数据的查询范围和结果形态。常用的连接方式分为内连接和外连接，外连接又可细分为左外连接与右外连接。本节将结合实例，拆解不同连接方式的语法、逻辑及适用场景。

6.1 内连接：只保留匹配的记录

内连接是最常用的连接方式，核心逻辑是只保留两张表中 “关联条件匹配” 的记录，不匹配的记录会被过滤掉。本质上，它等同于用where子句筛选两张表的笛卡尔积，我们之前学习的多表查询都属于内连接。

6.1.1 内连接语法

内连接支持两种语法格式，核心都是通过on指定关联条件（推荐用on，逻辑更清晰）：

1-- 格式1：显式内连接（推荐，明确标注 inner join）
2select 字段名 
3from 表1 inner join 表2 
4on 表1.关联字段 = 表2.关联字段  -- 核心：表间关联条件
5and 其他筛选条件;  -- 可选：对结果进一步筛选
6
7-- 格式2：隐式内连接（即之前的多表查询写法）
8select 字段名 
9from 表1, 表2 
10where 表1.关联字段 = 表2.关联字段  -- 用where代替on指定关联条件
11and 其他筛选条件;
12

6.1.2 内连接案例

需求：显示员工「SMITH」的姓名和所在部门名称（关联emp和dept表）。

方式 1：隐式内连接（笛卡尔积 + where 筛选）

1select e.ename, d.dname 
2from emp e, dept d 
3where e.deptno = d.deptno  -- 关联条件：员工部门号=部门表部门号
4  and e.ename = 'SMITH';  -- 筛选条件：员工姓名为SMITH
5

方式 2：显式内连接（inner join + on）

1-- 写法1：筛选条件放在on后
2select e.ename, d.dname 
3from emp e inner join dept d 
4on e.deptno = d.deptno  -- 关联条件
5and e.ename = 'SMITH';  -- 筛选条件
6
7-- 写法2：筛选条件放在where后（更易理解，先关联表再筛选）
8select e.ename, d.dname 
9from emp e inner join dept d 
10on e.deptno = d.deptno  -- 先通过on完成表关联
11where e.ename = 'SMITH';  -- 再通过where筛选目标员工
12

三种写法的查询结果一致：

1+-------+----------+
2| ename | dname    |
3+-------+----------+
4| SMITH | RESEARCH |
5+-------+----------+
61 row in set (0.00 sec)
7

6.2 外连接：保留某一张表的全部记录

外连接与内连接的核心区别是：会保留其中一张表的 “全部记录”，即使这些记录在另一张表中没有匹配项（无匹配的字段会显示NULL）。根据 “保留哪张表”，外连接分为左外连接和右外连接。

6.2.1 左外连接：保留左表全部记录

左外连接的逻辑是：以 “左表” 为基准，保留左表的所有记录，右表只保留与左表匹配的记录；若右表无匹配项，对应字段显示NULL。

1select 字段名 
2from 左表 left join 右表 
3on 左表.关联字段 = 右表.关联字段;  -- 关联条件（与内连接一致）
4

注：left join可省略outer（即left outer join），效果相同。

左外连接案例

为了更直观展示 “保留左表全部记录”，先创建两张测试表：stu（学生表）和exam（成绩表），其中部分学生无成绩，部分成绩无对应学生。

准备测试数据

1-- 1. 创建并插入学生表数据（左表，需保留全部学生）
2create table stu (id int, name varchar(30));
3insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
4
5-- 2. 创建并插入成绩表数据（右表，部分成绩无对应学生）
6create table exam (id int, grade int);
7insert into exam values(1,56),(2,76),(11,8);  -- id=11的成绩无对应学生
8

需求：查询所有学生的成绩，即使学生没有成绩也要显示其个人信息

1select s.id as 学生ID, s.name as 学生姓名, e.grade as 成绩 
2from stu s left join exam e 
3on s.id = e.id;  -- 关联条件：学生ID=成绩表ID
4

查询结果（关键：学生 kity、nono 无成绩，成绩字段显示 NULL，但仍保留记录）：

1+--------+----------+--------+
2| 学生ID | 学生姓名 | 成绩   |
3+--------+----------+--------+
4|      1 | jack     |     56 |
5|      2 | tom      |     76 |
6|      3 | kity     |   NULL |  -- 无成绩，显示NULL
7|      4 | nono     |   NULL |  -- 无成绩，显示NULL
8+--------+----------+--------+
94 rows in set (0.00 sec)
10

6.2.2 右外连接：保留右表全部记录

右外连接的逻辑与左外连接相反：以 “右表” 为基准，保留右表的所有记录，左表只保留与右表匹配的记录；若左表无匹配项，对应字段显示NULL。

1select 字段名 
2from 左表 right join 右表 
3on 左表.关联字段 = 右表.关联字段;  -- 关联条件
4

注：right join可省略outer（即right outer join），效果相同。

右外连接案例

需求：查询所有成绩记录，即使成绩没有对应学生也要显示成绩信息（以exam表为右表，保留全部成绩）。

方式 1：直接使用右外连接

1select s.id as 学生ID, s.name as 学生姓名, e.grade as 成绩 
2from stu s right join exam e 
3on s.id = e.id;  -- 关联条件：学生ID=成绩表ID
4

方式 2：等价于 “左表与右表互换的左外连接”右外连接可通过调换表的顺序，用左外连接实现（更符合直觉，推荐）：

1select s.id as 学生ID, s.name as 学生姓名, e.grade as 成绩 
2from exam e left join stu s  -- 成绩表作为左表，保留全部成绩
3on e.id = s.id;  -- 关联条件不变
4

两种写法的查询结果一致（关键：id=11 的成绩无对应学生，学生信息显示 NULL，但成绩记录保留）：

1+--------+----------+--------+
2| 学生ID | 学生姓名 | 成绩   |
3+--------+----------+--------+
4|      1 | jack     |     56 |
5|      2 | tom      |     76 |
6|   NULL | NULL     |     8  |  -- 无对应学生，显示NULL
7+--------+----------+--------+
83 rows in set (0.00 sec)
9

6.2.3 内连接与外连接的核心区别

为了更清晰区分，用表格对比三种连接方式的逻辑差异（以stu左表、exam右表为例）：

连接方式	保留的记录范围	无匹配项的处理	适用场景
内连接	只保留两表匹配的记录	不保留无匹配的记录	需获取 “双方都有数据” 的结果（如：有成绩的学生）
左外连接	保留左表全部记录，右表匹配记录	右表无匹配项显示 NULL	需 “以左表为基准”（如：所有学生的成绩，含无成绩的）
右外连接	保留右表全部记录，左表匹配记录	左表无匹配项显示 NULL	需 “以右表为基准”（如：所有成绩

要不要我帮你补充一份 MySQL 复合查询核心语法对照表？包含本文所有场景的语法模板、关键字说明和注意事项，方便你日常开发时直接查阅。

《MySQL 复合查询全解析：从单表到多表的实战进阶》是转载文章，点击查看原文。