12.4 相关子查询

      在前面几节中，我们学习的子查询大多是非相关子查询——它们独立于外部查询，可以单独运行，且只执行一次。然而，有一种更强大的子查询类型，它引用了外部查询的列，从而与外部查询产生了关联。这种子查询称为相关子查询（Correlated Subquery）。

一、什么是相关子查询？

相关子查询 是指子查询中使用了来自外部查询的列。由于子查询依赖于外部查询的当前行，它不能独立运行，而是必须为外部查询的每一行执行一次。

简单来说，相关子查询就像是一个“逐行检查”的过程：外部查询每处理一行，就带着这一行的值去执行一次子查询，判断是否满足条件。

基本语法

SELECT 列1, 列2, ...
FROM 表1 别名1
WHERE 表达式 运算符 (SELECT 聚合函数(列) 
                     FROM 表2 别名2 
                     WHERE 别名2.关联列 = 别名1.关联列);

关键点在于子查询的 WHERE 子句中使用了外部表的列（例如 别名2.关联列 = 别名1.关联列），建立了内外层的关联。

执行过程

为了更好地理解，我们通过一个例子来说明相关子查询的执行流程。

假设有两张表：

• employees（员工）：emp_id, name, salary, dept_id

• departments（部门）：dept_id, dept_name

需求：查询工资高于自己所在部门平均工资的员工。

我们可以用相关子查询实现：

SELECT e1.name, e1.salary, e1.dept_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

执行过程：

1. 外部查询从 employees e1 中取第一行（例如员工“张三”，部门1，工资8000）。

2. 将这一行的 dept_id（部门1）代入子查询，计算部门1的平均工资（假设为7000）。

3. 比较 8000 > 7000，条件成立，保留该行。

4. 接着处理外部查询的第二行，将它的 dept_id 代入子查询，再次计算该部门的平均工资……以此类推，直到所有行处理完毕。

可见，子查询的执行次数等于外部查询返回的行数（如果外部查询有1000行，子查询就要执行1000次）。这就是相关子查询可能带来性能问题的原因。

二、相关子查询的常见用法

1. 在 WHERE 子句中与比较运算符结合

这是最常用的形式，通常用于逐行比较聚合结果。

示例：找出工资低于本部门最高工资的员工。

SELECT name, salary, dept_id
FROM employees e1
WHERE salary < (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

2. 与 EXISTS / NOT EXISTS 结合

EXISTS 用于检查子查询是否返回任何行，非常适合判断存在性。

示例：查询有订单的客户。

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

这里子查询不需要返回具体数据，只需要知道是否有匹配行，因此通常写 SELECT 1 或 SELECT *，性能差异不大。

示例：查询从未下过单的客户（用 NOT EXISTS）。

SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

EXISTS 与 IN 有时可以互换，但 EXISTS 通常效率更高，尤其当子查询表很大时，因为它可以在找到第一个匹配后立即停止扫描。而且 EXISTS 不会受到 NULL 值的影响，比 NOT IN 更安全。

3. 在 SELECT 列表中使用（标量相关子查询）

相关子查询也可以出现在 SELECT 子句中，为每一行计算一个标量值。

示例：显示每个员工的姓名、工资以及所在部门的平均工资。

SELECT 
    name,
    salary,
    (SELECT AVG(salary) 
     FROM employees e2 
     WHERE e2.dept_id = e1.dept_id) AS dept_avg_salary
FROM employees e1;

注意：这种用法必须确保子查询返回单行单列，否则会报错。如果某个部门没有员工，子查询返回 NULL，那么该列的值为 NULL。

4. 在 HAVING 子句中使用

HAVING 用于分组后过滤，同样可以使用相关子查询。

示例：查询平均工资高于公司整体平均工资的部门。

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees e1
GROUP BY dept_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees e2
);

这个例子中的子查询是非相关的（因为它没有引用外部列），只是为了展示位置。如果要写相关子查询，例如“查询平均工资高于该部门内某个阈值的部门”，可能会需要相关子查询。

三、性能注意事项

相关子查询虽然灵活，但性能是主要关注点。由于子查询会为外部每一行执行一次，如果外部表很大，子查询又涉及全表扫描，查询可能会非常慢。

优化建议

1. 确保子查询中的关联列有索引。例如在上面的例子中，employees 表的 dept_id 应该建有索引，这样子查询可以快速定位到同一部门的行，避免全表扫描。

2. 尽量用 JOIN 重写。很多相关子查询可以用 JOIN + 分组的方式替代，通常效率更高。例如，查询工资高于本部门平均工资的员工，可以用派生表先计算平均工资，再连接：

SELECT e.name, e.salary, e.dept_id
FROM employees e
JOIN (SELECT dept_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY dept_id) dept_avg
      ON e.dept_id = dept_avg.dept_id
WHERE e.salary > dept_avg.avg_salary;

这种写法只需要扫描一次 employees 表用于分组，再扫描一次用于连接，通常比相关子查询逐行计算更快。

1. 使用 EXISTS 替代 IN。当子查询返回多行时，EXISTS 通常优于 IN，因为它可以利用索引并提前终止扫描。

2. 分析执行计划。使用 EXPLAIN 查看查询的执行方式，观察是否使用了索引，以及子查询的执行次数。MySQL 的优化器有时会将相关子查询转换为 JOIN 或物化为临时表，但并非总是如此。

四、相关子查询 vs 非相关子查询

独立性:

    相关子查询: 依赖外部查询的列

    非相关子查询: 完全独立，可单独运行

执行次数:

     相关子查询: 为外部每一行执行一次

     非相关子查询: 只执行一次

性能:

     相关子查询: 通常较慢，尤其表大时

     非相关子查询: 相对较快

常见位置:

     相关子查询: WHERE, SELECT, HAVING 中与 EXISTS 结合

     非相关子查询: WHERE, FROM, HAVING 等

常见位置:

     相关子查询:

WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id)

     非相关子查询: WHERE, FROM, HAVING 等

WHERE salary > (SELECT AVG(salary) FROM employees)

五、练习

1. 使用相关子查询查询出每个分类下价格最高的商品。表：products(id, name, price, category_id)

2. 使用 EXISTS 查询出所有有员工的部门。

3. 用 JOIN 重写第1题，并比较两种写法的优劣。

参考答案：

-- 1. 每个分类下价格最高的商品
SELECT p1.name, p1.price, p1.category_id
FROM products p1
WHERE p1.price = (
    SELECT MAX(p2.price)
    FROM products p2
    WHERE p2.category_id = p1.category_id
);

-- 2. 有员工的部门
SELECT d.dept_id, d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.dept_id
);

-- 3. JOIN 重写第1题
SELECT p.name, p.price, p.category_id
FROM products p
JOIN (
    SELECT category_id, MAX(price) AS max_price
    FROM products
    GROUP BY category_id
) maxp ON p.category_id = maxp.category_id AND p.price = maxp.max_price;

小结

• 相关子查询 通过引用外部表的列，实现了逐行关联计算。

• 常见用法包括：WHERE 中的比较、EXISTS 存在性检查、SELECT 中的标量子查询。

• 相关子查询可能带来性能问题，应谨慎使用，并通过索引和 JOIN 重写等手段优化。

• 与 EXISTS 配合时，通常比 IN 更高效，且能避免 NULL 陷阱。

理解相关子查询，能让你在需要复杂关联逻辑时游刃有余。下一节我们将学习联合查询，即如何将多个查询的结果合并在一起。