MySQL 复合查询实战：多表关联、子查询与合并技巧

二、多表查询：跨表关联核心玩法

多表查询是复合查询的基础，用于从多个关联表中提取数据。核心是通过'关联字段'消除笛卡尔积（无关联条件时，表 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 关联两张表：员工 + 部门信息

需求：查询员工姓名、工资及所在部门名称

-- 核心：通过 deptno 关联 emp 和 dept 表，消除笛卡尔积
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; -- 筛选 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）

-- 方法 1：子查询（简单场景）
SELECT empno, ename FROM emp WHERE empno = (SELECT mgr FROM emp WHERE ename = 'ford');

-- 方法 2：自连接（复杂场景更灵活）
SELECT leader.empno AS 领导编号，leader.ename AS 领导姓名 
FROM emp leader, emp worker -- leader=领导表，worker=员工表
WHERE leader.empno = worker.mgr -- 领导编号=员工的上级编号
AND worker.ename = 'ford'; -- 筛选员工为 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') -- 子查询返回 smith 的部门号
AND ename != 'smith'; -- 排除 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) -- 子查询返回 10 号部门的所有岗位
AND deptno != 10; -- 排除 10 号部门

4.2.2 ALL 关键字：大于 / 小于所有值

需求：查询工资比 30 号部门所有员工工资都高的员工

SELECT ename, sal, deptno FROM emp 
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30); -- 工资>30 号部门所有员工工资

4.2.3 ANY 关键字：大于 / 小于任意一个值

需求：查询工资比 30 号部门任意员工工资高的员工（含自身部门）

SELECT ename, sal, deptno FROM emp 
WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30); -- 工资>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 子句中的子查询：临时表用法

将子查询结果当作'临时表'，用于复杂统计分析（如先聚合再关联），核心是给临时表起别名。

实战案例 1：查询高于本部门平均工资的员工

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; -- 员工工资>部门平均工资

实战案例 2：查询各部门工资最高的员工

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 -- 自动去重（manager 中工资>2500 的员工只显示一次）
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 -- 保留重复行（manager 中工资>2500 的员可能会显示多次）
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;

真题 2：生成所有表的 count 查询语句

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）。

掌握这些复合查询技巧，能应对 90% 以上的业务场景。建议多结合实际需求练习，逐步提升 SQL 的灵活性和性能优化能力。后续会持续更新 MySQL 索引优化、事务、存储过程等进阶内容，带你从入门到精通数据库开发。