MySQL 嵌套子查询实战：从四种返回结果到多场景应用

Cora

发布时间：2026-10-16| 6人浏览过

原创

本文将详细解析 MySQL 嵌套子查询的核心概念与实战技巧。子查询允许在一个查询语句中嵌套另一个查询，以解决复杂的筛选需求。文章涵盖子查询的四种数据返回类型，并逐一演示如何在 WHERE、FROM 和 SELECT 子句中灵活运用这些结构，辅以完整的 SQL 代码示例。

子查询返回结果的四种类型

在编写嵌套子查询前，需明确内层查询可能返回的数据形态，这直接决定了外层查询该如何处理结果：

单行单列：返回一个具体的标量值，常用于比较运算（如 =, >, <）。
单行多列：返回一行记录中的多个字段值，通常用于成对的列比较或赋值。
多行单列：返回一列中的多个值，形成一个集合，常配合 IN, ANY, ALL 操作符使用。
多行多列：返回一个完整的临时结果集（虚拟表），通常在 FROM 子句中作为数据源使用。

在 WHERE 子句中使用子查询

这是子查询最常见的应用场景，主要用于对数据进行细粒度的过滤。

第1步：处理单行单列子查询

目的：利用极值（最大/最小）或聚合值（平均）作为过滤条件。

动作：

查询公司中工资最低的雇员完整信息。

-- 核心逻辑：外层查询 sal 等于内层查出的最低工资
SELECT * 
FROM emp e
WHERE e.sal = (
  SELECT MIN(sal) 
  FROM emp);

查询基本工资低于特定员工（如 ALLEN）的所有雇员。

SELECT * 
FROM emp e
WHERE e.sal < (
  SELECT sal 
  FROM emp 
  WHERE ename = 'ALLEN'
);

查询基本工资高于公司平均薪金的雇员。

SELECT *
FROM emp e
WHERE e.sal > (
  SELECT AVG(sal)
  FROM emp
);

第2步：处理单行多列子查询

目的：同时基于多个字段进行关联筛选。

动作：

分离列引用法：查找与 ALLEN 从事同一工作，且工资高于雇员编号为 7521 的员工。

SELECT *
FROM emp e
WHERE e.job = (
    SELECT job
    FROM emp 
    WHERE ename = 'ALLEN'
  ) 
  AND e.sal > (
  SELECT sal
  FROM emp 
  WHERE empno = 7521
);

元组比较法：查询与 SCOTT 工作相同且工资相同的其他员工。

SELECT *
FROM emp e
WHERE (e.job, e.sal) = (
    SELECT job, sal
    FROM emp 
    WHERE ename = 'SCOTT'
  )
  AND ename <> 'SCOTT';

函数转换法：查询与 ALLEN 从事同一工作且在同一年入职的员工。

SELECT *
FROM emp e
WHERE (e.job, TO_CHAR(e.hiredate, 'yyyy')) = (
       SELECT job, TO_CHAR(hiredate, 'YYYY')
       FROM emp
       WHERE ename = 'ALLEN'
);

第3步：处理多行单列子查询

目的：将单个字段值与一组值进行匹配判断，主要依赖 IN、NOT IN、ANY、ALL 等操作符。

动作：

查询出与每个部门中最低工资完全相同的雇员。

-- 内层查出各部门最低工资集合
SELECT * 
FROM emp e
WHERE e.sal IN (
     SELECT MIN(sal)
    FROM emp
    GROUP BY deptno
);

反向排除：查询出不属于任何一个部门最低工资水平的员工。

SELECT * 
FROM emp e
WHERE e.sal NOT IN (
     SELECT MIN(sal)
    FROM emp
    GROUP BY deptno
);

在 FROM 子句中进行子查询

原理说明：FROM 子句后的数据来源不仅是物理表，也可以是子查询的结果。此时，子查询返回的结果被视为一张“临时表”或“派生表”。必须为该临时表指定别名，且在使用其字段时需遵循 `别名.字段名` 的规范。

第4步：基于聚合结果的筛选

目的：先计算聚合数据（如平均工资），再对聚合结果进行过滤（HAVING 语义的替代方案）。

动作：查询平均工资高于 2000 的职位名称及对应薪资。

传统方式：使用 HAVING 子句。

SELECT job, AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) > 2000;

子查询方式：将分组聚合结果视为临时表 temp。

SELECT job, avgsal
FROM (
  SELECT job, AVG(sal) AS avgsal
  FROM emp
  GROUP BY job
) temp
WHERE temp.avgsal > 2000;

第5步：表连接与自连接辅助

动作：有时为了理清逻辑，可将部分复杂连接先封装在 FROM 子查询中，或者利用子查询构建中间数据流。例如查询有佣金且工资高于佣金的员工（此处使用常规 Join 也可实现，但展示了多表数据的组合思维）：

-- 注意：原文本中有混杂的非SQL注释，已去除干扰
SELECT e1.empno, e1.ename, e1.comm, e2.sal
FROM emp e1, emp e2
WHERE e1.comm IS NOT NULL 
  AND e1.comm < e2.sal
  AND e1.empno = e2.empno;

在 SELECT 列表中使用子查询

限制条件：SELECT 后面跟随的子查询必须是标量子查询，即只能返回单行单列的一个值。如果返回多行或多列，会导致 SQL 执行错误。

第6步：执行标量子查询

目的：在主查询的每一行处理过程中，动态获取关联子表的信息。

动作：

统计每个部门的员工总数。

-- 假设 departments 为主表，employees 为员工表
SELECT d.*, (
    SELECT COUNT(*)
    FROM employees
    WHERE employees.department_id = d.department_id
) AS employee_count
FROM departments d;

单独查询特定员工的部门名称（标量语境）：

-- 这种写法通常用于变量赋值或在存储过程中使用，直接在SELECT中列出如下:
SELECT (
  SELECT department_name
  FROM employees e
  INNER JOIN departments d ON e.department_id = d.department_id
  WHERE e.employee_id = 102
) AS dept_name;