MySQL子查询全解析：类型与实战示例

最新推荐文章于 2025-08-03 15:37:57 发布

六七_Shmily

最新推荐文章于 2025-08-03 15:37:57 发布

阅读量552

点赞数 7

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_80215285/article/details/149220864

数据库专栏收录该内容

26 篇文章

订阅专栏

MySQL 的子查询（Subquery），也称为嵌套查询（Nested Query），是指 在一个 SQL 查询语句内部嵌套另一个完整的 SELECT 查询语句。子查询的结果可以作为外层查询（主查询）的条件、数据源或计算字段的值。它是处理复杂查询逻辑的强大工具。

核心概念：

外层查询 (Outer Query)： 包含子查询的主要查询。
内层查询 (Inner Query / Subquery)： 嵌套在外层查询内部的 SELECT 语句。它先于外层查询执行。
作用域： 子查询可以访问其外层查询的列（相关子查询），也可以完全独立（不相关子查询）。

子查询的主要类型和用法：

根据子查询返回的结果集类型及其在外层查询中的使用位置，可以分为以下几类：

标量子查询 (Scalar Subquery)：

特点： 子查询只返回单个值（一行一列）。
使用位置： 可以出现在 SQL 语句中任何期望一个单值的地方。
- SELECT 列表（作为计算字段的一部分）
- WHERE 子句（与比较运算符 =, >, <, >=, <=, <> 等一起使用）
- HAVING 子句
- SET 子句（在 UPDATE 语句中）
- VALUES 子句（在 INSERT 语句中）

示例：

-- 1. 在SELECT列表: 查询每个员工及其所在部门的平均工资
SELECT e.name, e.salary,
       (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) AS dept_avg_sal
FROM employees e;
-- 2. 在WHERE子句: 查询工资高于公司平均工资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 3. 在HAVING子句: 查询平均工资高于部门30平均工资的部门
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees WHERE dept_id = 30);

列子查询 (Column Subquery / Row Subquery - 单列多行)：

特点： 子查询返回单列多行的结果集。
使用位置： 主要用在 WHERE 或 HAVING 子句中，需要与 多行比较运算符 配合使用。
- IN / NOT IN: 检查值是否在子查询返回的列表中。
- ANY / SOME: 与子查询返回的任意一个值比较满足条件即可。需配合 =, >, < 等单值比较符。
- ALL: 与子查询返回的所有值比较都必须满足条件。需配合 =, >, < 等单值比较符。

示例：

-- 1. 使用 IN: 查询在'New York'或'San Francisco'工作的员工
SELECT name
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE location IN ('New York', 'San Francisco')
);
-- 2. 使用 ANY/SOME: 查询工资高于部门30中任意一个员工工资的员工 (比部门30最低工资高即可)
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE dept_id = 30
);
-- 3. 使用 ALL: 查询工资高于部门30中所有员工工资的员工 (比部门30最高工资高)
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE dept_id = 30
);

行子查询 (Row Subquery)：

特点： 子查询返回单行多列的结果集（虽然可能只有一列，但本质是行）。
使用位置： 主要在 WHERE 子句中，使用行构造器 (Row Constructor) (col1, col2, ...) 与子查询返回的行进行比较。

示例：

-- 查找与员工ID为 101 的员工在同一个部门且担任同一个职位的其他员工
SELECT name, dept_id, job_title
FROM employees
WHERE (dept_id, job_title) = ( -- 行构造器比较
    SELECT dept_id, job_title
    FROM employees
    WHERE emp_id = 101
)
AND emp_id <> 101; -- 排除自己

表子查询 / 派生表 (Table Subquery / Derived Table)：

特点： 子查询返回多行多列的结果集，就像一个临时的虚拟表。
使用位置： 必须出现在 FROM 子句中。需要为这个派生表指定一个别名（Alias）。
用途： 简化复杂查询、分步计算、实现临时视图、连接聚合结果等。

示例：

-- 1. 计算每个部门的平均工资，并找出高于公司平均工资的部门
SELECT d.dept_name, d_avg.avg_salary
FROM departments d
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) d_avg ON d.dept_id = d_avg.dept_id
WHERE d_avg.avg_salary > (
    SELECT AVG(salary) FROM employees
);
-- 2. 使用派生表进行复杂的分页或TOP-N查询 (MySQL 8.0+ 更推荐用窗口函数)
SELECT *
FROM (
    SELECT emp_id, name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked_emps
WHERE rnk <= 5; -- 获取工资排名前5的员工

相关子查询 (Correlated Subquery)：
- 特点： 子查询的执行依赖于外层查询的当前行。子查询中引用了外层查询的列。
- 工作原理： 对于外层查询的每一行，数据库引擎都会执行一次子查询，使用该行中的相关列值作为子查询的条件。
- 性能注意： 因为需要为外层查询的每一行执行一次子查询，所以性能开销通常较大，尤其是在大表上。应谨慎使用并考虑优化（如使用 JOIN 或 EXISTS/NOT EXISTS）。
- 使用位置： 最常见于 WHERE 子句和 SELECT 列表（标量子查询形式）。
- 示例：
```
-- 1. 在WHERE子句: 查询那些工资高于其所在部门平均工资的员工 (标量相关子查询也可实现，但这是经典例子)
SELECT e1.name, e1.salary, e1.dept_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id -- 关联条件: 引用外层e1的dept_id
);
-- 2. 在SELECT列表: (前面标量子查询的示例就是相关的)
```
EXISTS 和 NOT EXISTS：
- 特点： 特殊的运算符，不关心子查询返回的具体数据，只关心子查询是否至少返回一行（EXISTS）或不返回任何行（NOT EXISTS）。
- 返回值： TRUE 或 FALSE。
- 与相关子查询结合： 非常常见且高效的模式，尤其适用于检查存在性关系。
- 性能： 数据库引擎一旦在子查询中找到匹配行（对于 EXISTS）或确认没有匹配行（对于 NOT EXISTS），就可以停止扫描，通常比 IN 或 COUNT(*) > 0 性能更好。
- 示例：
```
-- 1. EXISTS: 查询至少有一个订单的客户
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 -- 习惯写 SELECT 1, 实际内容不重要
    FROM orders o
    WHERE o.customer_id = c.customer_id -- 关联条件
);
-- 2. NOT EXISTS: 查询没有任何订单的客户
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```

子查询的关键注意事项和最佳实践：

括号： 子查询必须用圆括号 () 括起来。
别名 (Aliases)：
- 派生表 (FROM 子句中的子查询) 必须有别名。
- 为子查询中的列和表（尤其是在相关子查询中）使用有意义的别名，提高可读性并避免歧义。
性能优化：
- 避免滥用相关子查询： 它们通常比 JOIN 或非相关子查询慢很多。优先考虑使用 JOIN 或 EXISTS/NOT EXISTS 重写。
- EXISTS/NOT EXISTS vs IN/NOT IN：
  - 当子查询结果集很大时，EXISTS/NOT EXISTS 通常比 IN/NOT IN 性能更好，因为前者找到/找不到匹配即可停止，后者需要处理整个结果集。
  - NOT IN 陷阱： 如果子查询结果包含 NULL，NOT IN 会返回空结果（因为 NULL 不等于任何值，包括另一个 NULL）。NOT EXISTS 没有这个问题。处理可能含 NULL 的情况时，优先使用 NOT EXISTS。
- 索引： 确保子查询中用于连接 (JOIN 或关联条件) 和过滤 (WHERE) 的列上有合适的索引。
- 派生表优化： MySQL 5.7+ 特别是 8.0+ 对派生表（FROM 子句中的子查询）进行了大量优化（如派生表合并、物化）。理解版本差异。
可读性： 复杂的嵌套子查询会降低 SQL 的可读性和可维护性。尽量：
- 将复杂的子查询拆分成多个步骤，使用临时表或 WITH 子句 (Common Table Expressions, CTEs - MySQL 8.0+ 支持)。
- 使用适当的缩进和格式化。
- 考虑是否可以用 JOIN 更清晰地表达。

WITH 子句 (CTE - Common Table Expression)： (MySQL 8.0+)

提供了一种更清晰、模块化的方式来编写复杂查询，特别是涉及多个子查询或递归查询时。
可以显著提高复杂嵌套子查询的可读性和可维护性。

示例：

WITH dept_avgs AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
)
SELECT e.name, e.salary, d.dept_name, da.avg_sal
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN dept_avgs da ON e.dept_id = da.dept_id
WHERE e.salary > da.avg_sal;

总结：

MySQL 子查询是一个功能强大的特性，允许你构建非常灵活和复杂的查询逻辑。掌握不同类型的子查询（标量、列、行、表、相关）及其适用场景（SELECT, WHERE, FROM, EXISTS），并理解它们的性能影响（尤其是相关子查询和 IN vs EXISTS），是编写高效、可维护 SQL 的关键。始终优先考虑清晰性和性能，在可能的情况下用 JOIN 或 CTE 替代深层的嵌套子查询。