MySQL 复合查询实战：从多表联查到子查询，搞定复杂业务数据提取

        在实际开发中，单表查询无法满足 “跨表关联数据”“复杂条件筛选” 等需求（如 “查询员工及其部门信息”“找出工资高于部门平均水平的员工”）。MySQL 的复合查询通过 “多表联查”“自连接”“子查询”“合并查询” 等技术，能高效整合多个表的数据，提取精准业务结果。本文以 “公司管理系统”（EMP 员工表、DEPT 部门表、SALGRADE 工资等级表）为实战场景，系统讲解复合查询的核心用法，帮你解决 90% 的复杂查询需求。

一、复合查询基础：多表联查（避免笛卡尔积）

        多表联查是复合查询的基础，用于从多个表中提取关联数据。核心是通过 “关联字段”（如 EMP.deptno 与 DEPT.deptno）过滤无效的 “笛卡尔积”（多表未过滤时的全量组合），确保结果精准。

1.1 核心语法：多表联查的两种写法

写法 1：逗号分隔表，WHERE 过滤关联条件（传统写法）

SELECT 表1.字段1, 表2.字段2 
FROM 表1, 表2 
WHERE 表1.关联字段 = 表2.关联字段;

写法 2：JOIN 关键字（推荐，逻辑更清晰）

SELECT 表1.字段1, 表2.字段2 
FROM 表1 
INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

  INNER JOIN：仅保留两表中关联条件匹配的记录（常用）；

  LEFT JOIN：保留左表所有记录，右表无匹配时补 NULL；

  RIGHT JOIN：保留右表所有记录，左表无匹配时补 NULL。

1.2 实战案例

案例 1：查询员工姓名、工资及所在部门名

需求：数据来自 EMP（员工信息）和 DEPT（部门信息），关联字段为deptno（部门号）。

-- 传统写法：逗号分隔表，WHERE过滤关联条件
SELECT EMP.ename, EMP.sal, DEPT.dname 
FROM EMP, DEPT 
WHERE EMP.deptno = DEPT.deptno;

-- 推荐写法：INNER JOIN + ON
SELECT e.ename, e.sal, d.dname 
FROM EMP e  -- 表别名（简化代码）
INNER JOIN DEPT d ON e.deptno = d.deptno;

输出结果：每个员工对应的部门名（如 “SMITH” 对应 “RESEARCH” 部门）。

案例 2：查询 10 号部门的员工姓名、工资及部门名

在多表联查基础上，增加 “部门号 = 10” 的筛选条件：

SELECT e.ename, e.sal, d.dname 
FROM EMP e 
INNER JOIN DEPT d ON e.deptno = d.deptno 
WHERE d.deptno = 10;  -- 筛选10号部门

案例 3：查询员工姓名、工资及工资等级

需求：数据来自 EMP（工资 sal）和 SALGRADE（工资等级表，含 losal 最低工资、hisal 最高工资、grade 等级），关联条件为 “sal 在 losal 和 hisal 之间”。

SELECT e.ename, e.sal, s.grade 
FROM EMP e 
INNER JOIN SALGRADE s 
ON e.sal BETWEEN s.losal AND s.hisal;

输出结果：员工工资对应的等级（如 sal=5000 对应 grade=5）。

二、特殊多表联查：自连接（一张表查两次）

自连接是 “多表联查的特殊形式”—— 将同一张表当作两张表使用（通过别名区分），用于查询表内关联数据（如 “员工与其上级领导的关系”）。

2.1 核心逻辑

通过表别名将一张表拆分为 “主表” 和 “关联表”，例如将 EMP 表拆分为 “员工表（worker）” 和 “领导表（leader）”，关联字段为 “员工的 mgr（领导编号）” 与 “领导的 empno（员工编号）”。

2.2 实战案例：查询 FORD 的上级领导编号和姓名

需求：EMP 表中，mgr字段存储员工的领导编号，需找到 “FORD” 的领导信息。

-- 自连接：EMP表既作为员工表（worker），也作为领导表（leader）
SELECT 
    leader.empno AS 领导编号, 
    leader.ename AS 领导姓名 
FROM EMP leader, EMP worker  -- 表别名区分
WHERE 
    leader.empno = worker.mgr  -- 关联条件：领导的empno = 员工的mgr
    AND worker.ename = 'FORD';  -- 筛选员工FORD

输出结果：FORD 的领导是 “JONES”，编号 7566。

2.3 对比子查询写法

自连接也可用子查询实现，但自连接在复杂场景（如多条件关联）更高效：

-- 子查询写法：先查FORD的mgr，再查领导信息
SELECT empno, ename 
FROM EMP 
WHERE empno = (SELECT mgr FROM EMP WHERE ename = 'FORD');

三、灵活筛选：子查询（嵌套查询）

子查询是 “嵌套在其他 SQL 中的 SELECT 语句”，用于实现 “先算后查” 的逻辑（如 “先查部门平均工资，再找高于该工资的员工”）。根据返回结果，子查询分为 “单行子查询”“多行子查询”“多列子查询”。

3.1 单行子查询：返回 1 行 1 列结果

适用于 “等于、大于、小于” 等单值比较，常用运算符=、>、<。

案例：查询与 SMITH 同部门的员工

需求：先查 SMITH 的部门号，再查该部门的所有员工（不含 SMITH）。

SELECT * 
FROM EMP 
WHERE 
    deptno = (SELECT deptno FROM EMP WHERE ename = 'SMITH')  -- 子查询：SMITH的部门号
    AND ename <> 'SMITH';  -- 排除SMITH本人

3.2 多行子查询：返回多行 1 列结果

适用于 “在多个值中匹配” 的场景，需搭配IN、ALL、ANY关键字。

关键字	功能	示例
IN	匹配子查询返回的任意一个值	job IN (SELECT job FROM EMP WHERE deptno=10)
ALL	大于 / 小于子查询返回的所有值	sal > ALL (SELECT sal FROM EMP WHERE deptno=30)
ANY	大于 / 小于子查询返回的任意一个值	sal > ANY (SELECT sal FROM EMP WHERE deptno=30)

案例 1：查询与 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号部门

案例 2：查询工资高于 30 号部门所有员工的员工

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

3.3 多列子查询：返回多行多列结果

适用于 “多字段同时匹配” 的场景（如 “查询与 SMITH 部门和岗位都相同的员工”）。

案例：查询与 SMITH 部门和岗位完全相同的员工（不含 SMITH）

SELECT ename 
FROM EMP 
WHERE 
    (deptno, job) = (SELECT deptno, job FROM EMP WHERE ename='SMITH')  -- 多列匹配
    AND ename <> 'SMITH';

输出结果：与 SMITH 同部门（20）且同岗位（CLERK）的员工 “ADAMS”。

3.4 子查询作为临时表（FROM 子句中使用）

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

案例：查询每个员工的姓名、部门、工资及部门平均工资

需求：先统计各部门的平均工资（临时表），再与 EMP 表联查。

SELECT 
    e.ename, 
    e.deptno, 
    e.sal, 
    format(tmp.asal, 2) AS 部门平均工资  -- 格式化小数
FROM EMP e
INNER JOIN (
    -- 子查询：统计各部门平均工资，作为临时表tmp
    SELECT deptno, AVG(sal) AS asal 
    FROM EMP 
    GROUP BY deptno
) tmp ON e.deptno = tmp.deptno 
WHERE e.sal > tmp.asal;  -- 筛选工资高于部门平均的员工

四、结果整合：合并查询（UNION / UNION ALL）

当需要 “合并多个 SELECT 的结果”（如 “查询工资 > 2500 或岗位为 MANAGER 的员工”）时，使用UNION或UNION ALL，两者的核心区别是是否去重。

4.1 核心差异

关键字	功能	性能	适用场景
UNION	合并结果并自动去重	较低（需去重操作）	需避免重复结果（如统计唯一数据）
UNION ALL	合并结果但不去重	较高（无去重操作）	允许重复结果（如批量统计）

4.2 实战案例

案例 1：查询工资 > 2500 或岗位为 MANAGER 的员工（去重）

-- UNION：自动去掉重复记录（如JONES既是MANAGER，工资也>2500，仅显示一次）
SELECT ename, sal, job FROM EMP WHERE sal > 2500
UNION
SELECT ename, sal, job FROM EMP WHERE job = 'MANAGER';

案例 2：查询工资 > 2500 或岗位为 MANAGER 的员工（保留重复）

-- UNION ALL：保留重复记录（JONES会显示两次）
SELECT ename, sal, job FROM EMP WHERE sal > 2500
UNION ALL
SELECT ename, sal, job FROM EMP WHERE job = 'MANAGER';

注意：合并的多个 SELECT 语句，字段数量和类型必须一致（如第一个 SELECT 返回 3 个字段，后续也需返回 3 个字段，且类型匹配）。

五、复合查询实战：综合业务场景

结合上述技术，解决更复杂的业务需求，如 “查询每个部门的信息及员工数量”“找出每个部门工资最高的员工”。

5.1 案例 1：查询每个部门的部门名、编号、地址及员工数量

需求：数据来自 DEPT（部门信息）和 EMP（员工数量统计），用子查询生成员工数量临时表。

SELECT 
    d.deptno AS 部门编号, 
    d.dname AS 部门名, 
    d.loc AS 地址, 
    tmp.mycnt AS 员工数量 
FROM DEPT d
INNER JOIN (
    -- 子查询：统计各部门员工数量
    SELECT deptno, COUNT(*) AS mycnt 
    FROM EMP 
    GROUP BY deptno
) tmp ON d.deptno = tmp.deptno;

5.2 案例 2：查询每个部门工资最高的员工（姓名、工资、部门号）

需求：先统计各部门最高工资（临时表），再与 EMP 表联查匹配员工。

SELECT 
    e.ename AS 姓名, 
    e.sal AS 工资, 
    e.deptno AS 部门号 
FROM EMP e
INNER JOIN (
    -- 子查询：统计各部门最高工资
    SELECT deptno, MAX(sal) AS ms 
    FROM EMP 
    GROUP BY deptno
) tmp ON e.deptno = tmp.deptno AND e.sal = tmp.ms;  -- 匹配部门和最高工资

六、复合查询注意事项

1. 避免过度嵌套子查询：多层子查询（如 3 层以上）会降低性能，优先用 JOIN 改写；
2. 表别名的使用：多表联查或自连接时，必须用别名（如EMP e）区分表，否则 SQL 报错；
3. 关联条件的正确性：多表联查时，关联条件必须准确（如EMP.deptno = DEPT.deptno），否则会产生笛卡尔积（数据量暴增，性能骤降）；
4. NULL 值处理：子查询或联查中，若字段可能为 NULL（如 EMP.comm 奖金），需用IFNULL处理（如IFNULL(comm, 0)），避免计算结果为 NULL；
5. 索引优化：关联字段（如 deptno、empno）建议建立索引，提升多表联查效率。

七、总结

MySQL 复合查询是解决复杂业务查询的核心技术，核心要点如下：

1. 多表联查：通过INNER JOIN/LEFT JOIN+ON关联条件，提取跨表数据，避免笛卡尔积；
2. 自连接：将一张表拆分为两张表，查询表内关联数据（如员工与领导）；
3. 子查询：嵌套在其他 SQL 中，实现 “先算后查”，支持单行、多行、多列场景；
4. 合并查询：用UNION/UNION ALL整合多个 SELECT 结果，按需选择是否去重。