9.4 子查询：标量子查询、行子查询、表子查询、EXISTS 子查询

子查询（Subquery）就是嵌套在另一个 SQL 语句中的 SELECT 查询。它可以用在 SELECT、FROM、WHERE、HAVING 等子句中，让一条 SQL 完成多步骤的逻辑判断。用好子查询，能极大简化原本需要多次查询或程序拼接的复杂业务。

根据返回结果的形式，子查询通常分为四类：标量子查询、行子查询、表子查询和 EXISTS 子查询。理解它们的区别和适用场景，是写出高效 SQL 的关键一步。

9.4.1 标量子查询：返回单个值

标量子查询是最简单也最常用的一类，它返回的结果是“一个具体的值”——一行一列。 你可以把它当作一个普通的常量来使用，比如跟某个字段比较，或者作为 SELECT 列表中的一个计算列。

例如，我们希望查出所有薪资高于公司平均薪资的员工：

SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

这里子查询 (SELECT AVG(salary) FROM employees) 返回一个具体的数字，外层查询将它作为过滤条件。它等价于“先算出平均值，再用它去筛选”，但一条 SQL 就能完成。

标量子查询的使用位置非常灵活：

在 WHERE 中作为比较值：WHERE price > (SELECT AVG(price) FROM products)
在 SELECT 列表中作为计算列：比如同时显示每件商品及其所属分类的平均价格：

SELECT product_name, 
       price,
       (SELECT AVG(price) FROM products WHERE category_id = p.category_id) AS category_avg
FROM products p;

这个例子每输出一行商品，就会执行一次标量子查询去拿该类别的均价。对于小表还好，但对于大表可能效率较低，后面会讲如何优化。

在 HAVING 中配合聚合过滤：如查出总销售额超过全店平均水平的门店。

使用标量子查询时有一个硬性要求：子查询必须且只能返回一行一列。如果子查询返回多行，数据库会直接报错。比如你错误地写了一个可能返回多行的子查询放在比较符后面，查询将执行失败。为了避免这种情况，业务上要确保子查询结果唯一，或者用 LIMIT 1 强制定界。

9.4.2 行子查询：返回一行多列

行子查询返回的是一行记录，包含多个列的值。 它通常配合 =、<>、IN 等运算符，在 WHERE 中同时比较多个列的组合。

最典型的场景是：查找与某个已知记录在多个字段上完全匹配的其他行。例如，找出跟员工“张三”相同部门和相同职位的所有同事：

SELECT name 
FROM employees 
WHERE (department_id, position) = (
    SELECT department_id, position 
    FROM employees 
    WHERE name = '张三'
);

这里子查询返回了张三的部门 ID 和职位，外层用这“一对值”去匹配其他员工。写法上，列的组合用小括号括起来，顺序必须与子查询返回的列顺序一致。

行子查询也常和 IN 操作符一起使用，比如查找那些满足某种组合条件的数据，但标量子查询已经够满足大多数需求了。要注意：MySQL 对行子查询的优化不如标量子查询成熟，在数据量很大的时候，需要关注执行计划，看看是否会导致全表扫描。

9.4.3 表子查询：返回多行多列

表子查询返回的是一个结构完整的结果集（多行多列），通常用在 FROM 或 JOIN 中，作为一张“临时表”供外层查询使用，也叫做派生表（Derived Table）或内联视图。

比如，假设我们想统计每个部门薪资最高的员工信息。一个直观的思路是：先查出每个部门的最高薪资，再基于这个临时结果和原表关联取出完整记录。

SELECT e.name, e.department_id, e.salary 
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) AS dept_max
ON e.department_id = dept_max.department_id 
   AND e.salary = dept_max.max_salary;

这里的子查询 (SELECT department_id, MAX(salary) ...) 就是一个表子查询，它生成了一张包含部门 ID 和最高薪资的中间表 dept_max，然后外层查询用它做连接。

表子查询在实际开发中使用频率很高，但也需要留意几点：

派生表必须有别名：上面示例中的 AS dept_max 不可省略，MySQL 强制要求每个派生表都指定一个名称。
查询可能不会使用索引：派生表本质上是在内存或磁盘上临时构建的表，外层查询去关联它时不带原始表的索引。如果子查询结果集比较大，可能带来性能开销。MySQL 8.0 中优化器会自动尝试将某些派生表合并到外层查询（Derived Merge optimization），但这不是万能的。
可读性比性能更重要（有时）：遇到复杂分析，先写成派生表把逻辑拆清楚，再根据执行计划优化；过早优化牺牲可读性得不偿失。

还有一种书写方式：公共表表达式（CTE，即 WITH 子句），在 9.6 节会详细介绍。CTE 在功能上等价于派生表，但允许一个查询中多次引用同一个子查询结果，也支持递归，可读性更好。比如上面的查询用 CTE 来写：

WITH dept_max AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.department_id, e.salary
FROM employees e
JOIN dept_max ON e.department_id = dept_max.department_id 
             AND e.salary = dept_max.max_salary;

9.4.4 EXISTS 子查询：判断是否存在匹配行

EXISTS 子查询不关心返回的具体值，只关心“是否存在至少一行”。它是一个逻辑判断，结果要么是 TRUE 要么是 FALSE。

语法上，EXISTS (子查询) 前面不需要放列名，因为无需比较值。当子查询至少返回一行时，EXISTS 为真，外层算上这一行；如果子查询一行都没有，EXISTS 为假，外层舍弃该行。

最经典的应用场景是“找出有订单的所有客户”：

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

这里子查询带了一个关联条件 o.customer_id = c.customer_id——这种与外部查询列相关的子查询称为关联子查询。它的执行逻辑是：外层表每拿出一行数据，就把当前行的 c.customer_id 传入内层去检查 orders 表中是否有对应的订单。一旦内层找到了任何一条，就立刻返回 TRUE，不再继续扫描该 customer 在 orders 中的其他记录。这种“找到即停”的特性，叫短路特性，在很多情况下比普通 IN 子查询高效得多。

对于非关联 EXISTS，比如 WHERE EXISTS (SELECT * FROM settings WHERE key='maintenance')，它就是一个全局条件判断，不随外层行变化。

除了 EXISTS，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 vs IN：如何选择？

这也是面试和实际开发中的高频问题。以下几点帮助你决策：

当内表查询结果集较小时，IN 可能更快，因为 MySQL 会先把子查询求值出一个列表，外层再判断是否在其中。
当外层表很小，而内表按关联列有索引时，EXISTS 利用短路特性，逐个匹配效率更高。
当子查询可能返回 NULL 时，IN 和 NOT IN 行为要小心：如果子查询结果集包含 NULL，那么 NOT IN 整体会变成空集（因为任何值与 NULL 的比较结果都是 UNKNOWN）。这种情况下，NOT EXISTS 通常更安全、更符合直觉。

所以，不需要死记答案，而是根据数据分布和索引情况去判断，或者用 EXPLAIN 看执行计划再做选择。MySQL 优化器在 8.0 中已经能自动转换一些 IN 和 EXISTS 的写法，但明确它们的语义，能帮你写出更清晰的业务 SQL。

最后提一点优化：对于关联子查询，如果内层查询的表很大，务必保证关联列上有索引。比如上面例子中，orders.customer_id 应该有索引，否则 EXISTS 的效果会退化成对每个客户全表扫描订单表，效率极差。

小结：子查询是构建复杂查询的基本工具。标量子查询侧重“值”；行子查询侧重“组合匹配”；表子查询侧重“临时结果集复用”；EXISTS 子查询侧重“存在性判断”。根据具体业务场景选择合适的子查询类型，并关注索引和执行计划，就能在清晰表达逻辑的同时保证性能。