MySQL——子查询用法详解

一、子查询的基本概念

子查询（Subquery）是指嵌套在其他SQL查询语句中的查询语句，也称为内部查询（Inner Query）或嵌套查询（Nested Query）。子查询可以出现在SELECT、FROM、WHERE、HAVING等子句中，其核心作用是为外部查询提供数据或条件支持。

从执行顺序来看，MySQL会先执行子查询，获取结果后再将其结果作为外部查询的输入。这种嵌套结构使得开发者能够构建复杂的逻辑关系，实现多表关联、条件过滤等高级功能。

1.1 子查询的分类

根据子查询返回的结果集特征，可将其分为以下三类：

• 标量子查询：返回单个值（如一个数字、字符串或日期），适用于WHERE、SELECT等需要标量值的场景。
• 行子查询：返回单行多列数据，通常用于与外部查询的行进行匹配。
• 表子查询：返回多行多列的结果集，可作为临时表参与外部查询的连接或过滤。

二、子查询的应用场景

2.1 WHERE子句中的子查询

WHERE子句中的子查询常用于条件过滤，其典型形式包括：

• IN/NOT IN操作符：判断外部查询的值是否存在于子查询结果中。

  -- 查询销售过iPhone的订单
  SELECT order_id FROM orders 
  WHERE product_id IN (SELECT product_id FROM products WHERE product_name = 'iPhone');

• 比较操作符：通过=、>、<等操作符与子查询结果比较。

  -- 查询销售额高于平均值的订单
  SELECT order_id, amount FROM orders 
  WHERE amount > (SELECT AVG(amount) FROM orders);

• EXISTS/NOT EXISTS：判断子查询是否返回结果，适用于存在性检查。

  -- 查询有未付款订单的客户
  SELECT customer_id FROM customers c
  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'unpaid');

2.2 FROM子句中的子查询

FROM子句中的子查询（派生表）将子查询结果作为临时表使用，需为派生表指定别名：

-- 查询各部门平均工资及与全公司平均的差值
SELECT d.department_name, 
       avg_salary.dept_avg,
       avg_salary.dept_avg - (SELECT AVG(salary) FROM employees) AS diff
FROM departments d
JOIN (
    SELECT department_id, AVG(salary) AS dept_avg 
    FROM employees 
    GROUP BY department_id
) AS avg_salary ON d.department_id = avg_salary.department_id;

2.3 SELECT子句中的子查询

SELECT子句中的子查询（标量子查询）用于为每行返回计算值：

-- 查询员工工资及其在部门中的排名
SELECT employee_id, name, salary,
       (SELECT COUNT(DISTINCT salary) 
        FROM employees e2 
        WHERE e2.department_id = e1.department_id 
        AND e2.salary >= e1.salary) AS dept_rank
FROM employees e1;

三、子查询的性能优化

3.1 子查询与JOIN的权衡

子查询和JOIN均可实现多表关联，但性能特征不同：

• IN子查询：MySQL 5.6+优化为半连接（Semi-join），性能接近JOIN。
• EXISTS子查询：适用于子查询结果集较大时，按需检索外部表数据。
• JOIN操作：当需要多列数据或复杂关联时更高效。

优化建议：

• 对大表关联优先测试JOIN性能
• 使用EXPLAIN分析子查询执行计划
• 考虑将频繁使用的子查询重构为视图

3.2 相关子查询优化

相关子查询（Correlated Subquery）是指子查询引用外部查询的列，这类查询可能产生性能问题：

-- 低效的相关子查询示例
SELECT employee_id, name 
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);

优化方案：

1. 改用JOIN重写：

   SELECT e1.employee_id, e1.name
   FROM employees e1
   JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
   ) e2 ON e1.department_id = e2.department_id
   WHERE e1.salary > e2.avg_salary;

2. 使用派生表或临时表缓存中间结果

3.3 索引利用策略

为子查询涉及的字段创建适当索引：

• 对WHERE子句中的子查询条件列建索引
• 对JOIN操作的关联字段建索引
• 避免在索引列上使用函数导致索引失效

四、高级子查询技术

4.1 多列子查询

MySQL支持返回多列的子查询，常用于行比较：

-- 查询工资与部门平均工资相同的员工
SELECT employee_id, name 
FROM employees e
WHERE (department_id, salary) IN (
    SELECT department_id, AVG(salary)
    FROM employees
    GROUP BY department_id
);

4.2 LATERAL JOIN（MySQL 8.0+）

MySQL 8.0引入LATERAL关键字，允许派生表引用前面表中的列：

-- 查询每个部门工资最高的员工
SELECT d.department_name, e.name, e.salary
FROM departments d
CROSS JOIN LATERAL (
    SELECT name, salary
    FROM employees
    WHERE department_id = d.department_id
    ORDER BY salary DESC
    LIMIT 1
) e;

4.3 递归子查询（CTE）

MySQL 8.0支持WITH RECURSIVE实现递归查询：

-- 查询组织架构层级
WITH RECURSIVE dept_tree AS (
    SELECT department_id, department_name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.department_id, d.department_name, d.parent_id, dt.level + 1
    FROM departments d
    JOIN dept_tree dt ON d.parent_id = dt.department_id
)
SELECT * FROM dept_tree ORDER BY level, department_id;

五、最佳实践建议

1. 简化复杂子查询：将多层嵌套子查询拆分为多个简单查询或使用临时表
2. 控制子查询结果集大小：在子查询中添加WHERE条件限制返回行数
3. 避免SELECT *：在子查询中明确指定所需列
4. 定期分析查询计划：使用EXPLAIN ANALYZE查看实际执行情况
5. 考虑物化视图：对频繁执行的复杂子查询创建物化视图

结语

MySQL子查询是构建复杂SQL查询的强大工具，合理运用可显著提升查询表达能力。开发者需根据具体场景选择子查询类型，并通过性能分析持续优化。随着MySQL版本升级，新的语法特性（如LATERAL JOIN、递归CTE）为子查询应用提供了更多可能性，值得深入探索。