SQL 子查询有哪些类型？相关子查询是如何执行的？

简化版

子查询可按返回形态分为标量、单列、多列和表子查询，也可按是否引用外层列分为非相关与相关子查询。相关子查询在逻辑上针对外层当前行求值，但优化器可能将其改写为连接、半连接或物化，并不意味着物理上必然逐行完整执行。

详细版

常见位置包括 WHERE、HAVING、SELECT 和 FROM：

-- 标量子查询：必须至多返回一个值
SELECT name, (SELECT AVG(salary) FROM employee) AS company_avg
FROM employee;

-- 相关子查询：引用外层 e.department_id
SELECT e.*
FROM employee AS e
WHERE salary > (
  SELECT AVG(e2.salary)
  FROM employee AS e2
  WHERE e2.department_id = e.department_id
);

标量子查询返回多行通常会报错；没有行时结果通常视为 NULL。FROM 中的子查询形成派生表，必须按数据库语法提供别名。

完整版教学

一、按返回形态分类

标量子查询：一行一列，用在需要单个值的位置；
单列子查询：多行一列，常配合 IN、ANY、ALL；
行子查询：返回一行多列，可与行值表达式比较；
表子查询：返回多行多列，常放在 FROM 中作为派生表。

“子查询能返回什么”决定外层可以用什么运算符。把多行结果交给 =，数据库无法确定应与哪一行比较，通常会报错。

二、非相关子查询与相关子查询

非相关子查询不引用外层列，可以独立运行：

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

三、SELECT 中的标量子查询要防止多行

SELECT c.id,
       (SELECT o.created_at
        FROM orders AS o
        WHERE o.customer_id = c.id) AS order_time
FROM customer AS c;

若客户有多笔订单，标量位置收到多行会报错。随意加 LIMIT 1 只是压住错误；没有 ORDER BY 时还无法确定取哪一笔。正确做法是先明确业务规则，例如取最新时间可用 MAX(created_at)，或使用带稳定排序的窗口函数。

四、派生表与 CTE

SELECT d.department_id, d.avg_salary
FROM (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employee
  GROUP BY department_id
) AS d
WHERE d.avg_salary > 10000;

派生表把中间结果当作一张临时关系供外层查询。CTE 能为同类中间结果命名，提高复杂查询的可读性。两者是否实际物化由数据库、版本和查询结构决定，不应默认“写了子查询就一定生成磁盘临时表”。

五、子查询和 JOIN 怎么选

能互相改写不代表必须统一成一种形式。只判断存在性时 EXISTS 很清楚；需要拿到关联表列时 JOIN 更自然；需要先聚合再关联时派生表或 CTE 往往更易读。

性能判断要看执行计划。重点检查子查询能否去相关、关联列索引、估算行数，以及是否发生重复扫描或大规模物化。

六、常见误区与追问

分类维度	类型	面试判断点
返回形态	标量/行/表子查询	能放在哪个 SQL 位置
相关性	相关/非相关子查询	是否引用外层行
所在位置	`SELECT`/`WHERE`/`FROM`	语义和可读性不同
优化可能	去相关/物化/半连接	需要看执行计划

误区：子查询一定比 JOIN 慢。 优化器可能把子查询改写成连接、半连接或物化结果，性能取决于计划、索引和数据量。
误区：标量子查询返回多行时数据库会自动取第一行。 标量位置要求最多一行一列，多行通常会报错；需要明确聚合、排序取一条或改写关系。
误区：相关子查询一定逐行暴力执行。 逻辑上依赖外层行，但优化器可能做去相关、缓存或半连接改写，不能只凭写法判断成本。
追问：IN 子查询为什么只能返回一列？ 单列 IN 比较的是一个值与一个值集合；多列比较要用行值表达式或改写成 EXISTS/JOIN。
追问：派生表和 CTE 有什么差别？ 派生表写在 FROM 里，CTE 写在 WITH 里并可被多处引用；是否物化不是语法名称单独决定的。
追问：什么时候用 EXISTS 替代 JOIN？ 只需要判断是否存在关联行、不需要取右表列时，EXISTS 能避免无意中把一对多连接放大。

记忆钩子：判断子查询先看“返回几行几列”，再看“是否引用外层”，最后才比较 JOIN 改写和执行计划。

七、加强记忆

先看子查询返回几行几列，再决定能放在哪里、配什么运算符；再看它是否引用外层列，判断是不是相关子查询。相关只描述语义依赖，不等于物理上必然逐行重跑，最终执行方式由优化器和执行计划决定。

简化版

详细版