跳到主要内容
MySQL 复合查询核心指南：多表、子查询与实战技巧 | 极客日志
SQL 算法
MySQL 复合查询核心指南：多表、子查询与实战技巧 MySQL 复合查询涵盖多表关联、自连接、子查询及合并查询。通过关联字段消除笛卡尔积实现跨表数据提取；自连接处理同表层级关系；子查询支持嵌套筛选与临时表统计；Union 用于结果集合并。掌握关联条件、别名管理及性能优化要点，可应对复杂业务场景下的数据检索需求。
古灵精怪 发布于 2026/3/26 更新于 2026/6/2
文章目录
前言：
在实际开发中，单表查询远不能满足复杂业务需求 —— 员工信息散落在员工表、部门表、薪资等级表中，需要跨表关联才能获取完整数据；统计分析时需嵌套查询筛选条件；多结果集合并需用到集合操作符。
一。基础回顾：复合查询的前置知识
在学习复杂复合查询前，先回顾基础查询的核心语法，为后续进阶打基础：
select * from emp where
>
500
or
=
'manager'
and
like
'j%'
select
*
from
order
by
asc
desc
select
*
12
+
0
as
from
order
by
desc
select
avg
2
as
max
as
from
group
by
select
avg
as
from
group
by
having
<
2000
二。多表查询：跨表关联核心玩法 多表查询是复合查询的基础，用于从多个关联表中提取数据，核心是通过'关联字段'消除笛卡尔积（无关联条件时，表 1 所有行与表 2 所有行组合，数据量爆炸）。
2.1 测试表结构 本次实战基于 3 张经典表，先明确表结构和关联关系：
emp（员工表） ：存储员工基本信息，关联字段 deptno（关联部门表）、sal（关联薪资等级表）；dept（部门表） ：存储部门信息，关联字段 deptno；salgrade（薪资等级表） ：存储薪资等级规则，关联字段 losal（最低工资）、hisal（最高工资）。
2.2 多表查询核心语法 select 表 1. 字段，表 2. 字段 from 表 1 , 表 2
where 表 1. 关联字段 = 表 2. 关联字段 [and 其他筛选条件];
2.3 实战案例
2.3.1 关联两张表：员工 + 部门信息
select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno = dept.deptno;
2.3.2 多表 + 条件筛选：指定部门员工信息 需求：查询 10 号部门的员工姓名、工资及部门名称
select emp.ename, emp.sal, dept.dname from emp, dept
where emp.deptno = dept.deptno and dept.deptno = 10 ;
2.3.3 关联多张表：员工 + 薪资等级 select emp.ename, emp.sal, salgrade.grade from emp, salgrade
where emp.sal between salgrade.losal and salgrade.hisal;
2.4 多表查询避坑点
必须加关联条件 ：无关联条件会产生笛卡尔积（如 emp 有 14 行、dept 有 4 行，会产生 14×4=56 行无效数据）；字段歧义需加表名前缀 ：若多个表有同名字段（如 deptno），需用表名。字段区分；关联字段类型必须一致 ：emp.deptno 和 dept.deptno 需同为 int 类型，否则关联失效。
三。自连接：同表关联查询 自连接是多表查询的特殊形式 —— 将同一张表当作两张表使用，通过别名区分，适用于查询表内关联数据（如员工与上级领导的关系）。
3.1 核心语法 select 表别名 1. 字段，表别名 2. 字段 from 表 表别名 1 , 表 表别名 2
where 表别名 1. 关联字段 = 表别名 2. 关联字段 [and 筛选条件];
3.2 实战案例：查询员工的上级领导 需求：查询员工 ford 的上级领导编号和姓名（emp 表中 mgr 字段是领导的 empno）
select empno, ename from emp where empno = (select mgr from emp where ename= 'ford' );
select leader.empno as 领导编号，leader.ename as 领导姓名
from emp leader, emp worker
where leader.empno = worker.mgr
and worker.ename = 'ford' ;
3.3 自连接关键技巧
必须给表起不同别名（如 leader、worker），否则 MySQL 无法区分两张'虚拟表'；
关联字段需是表内的关联关系（如员工表的 mgr 与自身的 empno）。
四。子查询：嵌套查询的灵活用法 子查询（嵌套查询）是指嵌入在其他 SQL 语句中的 select 语句，按返回结果可分为单行、多行、多列子查询，按位置可分为 where 子句、from 子句中的子查询。
4.1 单行子查询：返回 1 行 1 列结果 适用于筛选条件为'等于、大于、小于'单个值的场景，常用比较运算符（=、>、<、>=、<=）。
实战案例：
需求：查询与 smith 同部门的所有员工（不含 smith）
select * from emp
where deptno = (select deptno from emp where ename= 'smith' )
and ename != 'smith' ;
4.2 多行子查询：返回多行 1 列结果 适用于筛选条件为'在多个值中''大于所有值''大于任意值'的场景，常用关键字 in、all、any。
4.2.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 ;
4.2.2 all 关键字：大于 / 小于所有值 需求：查询工资比 30 号部门所有员工工资都高的员工
select ename, sal, deptno from emp
where sal > all (select sal from emp where deptno= 30 );
4.2.3 any 关键字：大于 / 小于任意一个值 需求：查询工资比 30 号部门任意员工工资高的员工（含自身部门）
select ename, sal, deptno from emp
where sal > any (select sal from emp where deptno= 30 );
4.3 多列子查询：返回多行多列结果 适用于筛选条件需匹配'多个字段组合'的场景，子查询返回多列，主查询用括号接收字段组合。
实战案例：
需求：查询与 smith 部门和岗位完全相同的员工（不含 smith）
select ename from emp
where (deptno, job) = (select deptno, job from emp where ename= 'smith' )
and ename != 'smith' ;
4.4 from 子句中的子查询：临时表用法 将子查询结果当作'临时表'，用于复杂统计分析（如先聚合再关联），核心是给临时表起别名。
select emp.ename, emp.deptno, emp.sal, format(tmp.asal, 2 ) as 部门平均工资
from emp, (select avg (sal) as asal, deptno as dt from emp group by deptno) tmp
where emp.deptno = tmp.dt
and emp.sal > tmp.asal;
select emp.ename, emp.sal, emp.deptno, tmp.ms as 部门最高工资
from emp, (select max (sal) as ms, deptno from emp group by deptno) tmp
where emp.deptno = tmp.deptno and emp.sal = tmp.ms;
4.5 子查询避坑指南
单行子查询只能用单行运算符 ：若子查询返回多行，不能用 =，需用 in；from 子句的子查询必须起别名 ：MySQL 要求临时表必须有别名，否则报错；子查询尽量简化 ：复杂子查询可拆分为临时表或多步查询，提升可读性和性能。
五。合并查询：union 与 union all 合并查询用于将多个 select 语句的结果集合并为一个，适用于多条件独立查询后合并结果的场景，核心是 union（去重）和 union all（不去重）。
5.1 核心语法
select 字段 from 表 1 where 条件 union
select 字段 from 表 2 where 条件;
select 字段 from 表 1 where 条件 union all
select 字段 from 表 2 where 条件;
5.2 实战案例 案例 1：union 去重合并
需求：查询工资 > 2500 或岗位为 manager 的员工（去重）
select ename, sal, job from emp where sal > 2500 union
select ename, sal, job from emp where job= 'manager' ;
案例 2：union all 不去重合并
需求：查询工资 > 2500 或岗位为 manager 的员工（保留重复）
select ename, sal, job from emp where sal > 2500 union all
select ename, sal, job from emp where job= 'manager' ;
六。实战 OJ 真题：复合查询落地应用 结合牛客网经典 OJ 题，练习复合查询的实际应用：
真题 1：查找所有员工入职时的薪水情况（emp_no+salary，逆序）
select e.emp_no, s.salary from employees e, salaries s
where e.emp_no = s.emp_no and s.from_date = e.hire_date
order by e.emp_no desc ;
select concat('select count(*) from ' , table_name, ';' ) as count_sql
from information_schema.tables where table_schema = 'your_database_name' ;
真题 3：获取所有非 manager 的员工 emp_no
select emp_no from employees where emp_no not in (select emp_no from dept_manager);
真题 4：获取所有员工当前的 manager（排除 manager 是自己的情况）
select e.emp_no, d.emp_no as manager from dept_emp as e, dept_manager as d
where e.dept_no = d.dept_no and e.emp_no != d.emp_no;
七。总结 MySQL 复合查询是解决复杂业务需求的核心，核心要点总结：
多表查询 ：通过关联字段消除笛卡尔积，适用于跨表提取数据；自连接 ：同表当作两张表，适用于表内关联（如员工与领导）；子查询 ：嵌套在 where/from 子句中，灵活筛选和统计，需注意单行 / 多行匹配规则；合并查询 ：union（去重）和 union all（不去重），适用于多结果集合并；避坑关键 ：关联字段一致、临时表起别名、优先选择高效语法（如 union all 替代 union）。 相关免费在线工具 加密/解密文本 使用加密算法（如AES、TripleDES、Rabbit或RC4）加密和解密文本明文。 在线工具，加密/解密文本在线工具，online
Gemini 图片去水印 基于开源反向 Alpha 混合算法去除 Gemini/Nano Banana 图片水印，支持批量处理与下载。 在线工具，Gemini 图片去水印在线工具，online
SQL 美化和格式化 在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
SQL转CSV/JSON/XML 解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online
CSV 工具包 CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
Base64 字符串编码/解码 将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
