进阶查询（窗口函数 / CTE / 递归 / 子查询） · 子查询与相关子查询

标量子查询、列子查询、行子查询、表子查询各自示例？

Examples of scalar, column, row, and table subqueries?

答案语言

考察要点

这道题考察的是候选人对 SQL 查询基本构建块的理解，特别是对子查询（Subquery）的分类和使用场景的掌握程度。面试官想看你是否能清晰地分辨：

不同返回结果：子查询返回的是单个值（标量）、一列值、一行值，还是一张虚拟表。
不同语法位置：根据返回结果的不同，这些子查询分别能用在 SELECT、FROM、WHERE、HAVING 等子句的什么位置。
核心语义：是否理解 IN, EXISTS, =, ANY, ALL 等操作符与不同子查询结合时的语义。

核心概念

子查询是嵌套在另一个 SQL 查询（主查询）中的查询。根据其返回结果的形态，可以分为四种类型。

为了方便演示，我们使用以下两张表： employees 表 (员工表)

id	name	salary	dept_id
1	Alice	90000	1
2	Bob	80000	2
3	Charlie	75000	1
4	David	100000	2
5	Eve	120000	3

departments 表 (部门表)

id	dept_name
1	Engineering
2	Sales
3	Management

1. 标量子查询 (Scalar Subquery)

定义：返回结果为单行单列的子查询，即一个单一的值。
使用位置：几乎可以用在任何需要单一值的地方，如 SELECT 列表、WHERE 子句、HAVING 子句、ORDER BY 子句。
示例：查询所有员工及其公司的平均薪资。
sql
```
 1SELECT 
 2    name, 
 3    salary,
 4    (SELECT AVG(salary) FROM employees) AS company_avg_salary
 5FROM employees;
```
输出结果：
name salary company_avg_salary
Alice 90000 93000.00
Bob 80000 93000.00
Charlie 75000 93000.00
David 100000 93000.00
Eve 120000 93000.00
边界条件：
- 如果子查询返回超过一行，会报错 Error: Subquery returns more than 1 row。
- 如果子查询不返回任何行，其结果为 NULL。

name	salary	company_avg_salary
Alice	90000	93000.00
Bob	80000	93000.00
Charlie	75000	93000.00
David	100000	93000.00
Eve	120000	93000.00

2. 列子查询 (Column Subquery)

定义：返回结果为单列多行的子查询，即一个值的列表。
使用位置：通常用在 WHERE 子句中，与 IN、NOT IN、ANY、ALL 等操作符结合使用。
示例：查询所有在 'Engineering' 或 'Sales' 部门工作的员工。
sql
```
 1SELECT name, salary
 2FROM employees
 3WHERE dept_id IN (SELECT id FROM departments WHERE dept_name IN ('Engineering', 'Sales'));
```
输出结果：
name salary
Alice 90000
Bob 80000
Charlie 75000
David 100000
边界条件：
- 与 NOT IN 结合使用时要特别小心 NULL。如果列子查询的结果中包含 NULL，col NOT IN (val1, val2, NULL) 的结果永远不会为 TRUE，可能导致查询返回非预期的空集。推荐使用 NOT EXISTS 替代。

name	salary
Alice	90000
Bob	80000
Charlie	75000
David	100000

3. 行子查询 (Row Subquery)

定义：返回结果为单行多列的子查询，即一个记录行。
使用位置：通常用在 WHERE 或 HAVING 子句中，进行行级别的比较。
示例：查询与 'Alice' 在同一个部门且薪资也相同的员工（虽然此场景业务上少见，但能清晰演示语法）。
sql
```
 1SELECT name, salary, dept_id
 2FROM employees
 3WHERE (dept_id, salary) = (SELECT dept_id, salary FROM employees WHERE name = 'Alice');
```
输出结果：
name salary dept_id
Alice 90000 1
版本差异：行构造器语法 (col1, col2) = ... 是 SQL 标准的一部分，在现代数据库如 MySQL 5.7+、PostgreSQL、Oracle 中都得到良好支持。在非常古老的 MySQL 版本中可能支持不佳。

name	salary	dept_id
Alice	90000	1

4. 表子查询 (Table Subquery)

定义：返回结果为多行多列的子查询，即一张虚拟表。
使用位置：主要用在 FROM 子句中，作为派生表（Derived Table）或内联视图（Inline View）。

示例：查询每个部门的平均薪资。

sql

 1SELECT
 2    d.dept_name,
 3    t.avg_salary
 4FROM (
 5    SELECT dept_id, AVG(salary) AS avg_salary
 6    FROM employees
 7    GROUP BY dept_id
 8) AS t -- 必须为表子查询指定别名
 9JOIN departments d ON t.dept_id = d.id;

输出结果：

dept_name	avg_salary
Engineering	82500.00
Sales	90000.00
Management	120000.00

边界条件：
- 在 FROM 子句中使用时，绝大多数数据库（包括 MySQL）强制要求必须为其指定一个别名（如上例中的 AS t）。

常见低分回答

混淆 = 和 IN：对列子查询使用 = 而不是 IN。

sql

 1-- 错误：如果子查询返回多行，会直接报错
 2SELECT * FROM employees WHERE dept_id = (SELECT id FROM departments);

忘记给派生表加别名：在 FROM 子句中使用表子查询但没有指定别名。

sql

 1-- 错误：在 MySQL 中会报语法错误 "Every derived table must have its own alias"
 2SELECT * FROM (SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id);

滥用相关子查询导致性能低下：在 SELECT 列表中使用相关子查询，本可以用 JOIN 更高效地完成。

sql

 1-- 低效：对 employees 表的每一行都会执行一次子查询
 2SELECT 
 3    e.name,
 4    (SELECT d.dept_name FROM departments d WHERE d.id = e.dept_id) AS department_name
 5FROM employees e;
 6-- 更优：使用 JOIN
 7SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.id;

NOT IN 踩坑：当子查询结果集可能包含 NULL 时，错误地使用 NOT IN。

sql

 1-- 假设 departments 表中有一行的 id 是 NULL
 2-- 这个查询将永远返回空集，因为 `dept_id NOT IN (1, 2, NULL)` 的判断结果是 UNKNOWN
 3SELECT * FROM employees WHERE dept_id NOT IN (SELECT id FROM departments);
 4-- 正确做法是使用 NOT EXISTS 或在子查询中过滤 NULL
 5SELECT * FROM employees e WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

工程实践与优化

相关子查询 vs 非相关子查询：
- 非相关子查询：子查询可以独立于主查询执行，只执行一次。其结果被主查询使用。例如上面所有的示例都是非相关子查询。
- 相关子查询：子查询的执行依赖于主查询的当前行。主查询每处理一行，子查询就要重新执行一次。这通常导致性能问题。
- 识别：EXPLAIN 的 Extra 字段中出现 Dependent Subquery 意味着这是一个无法被优化器解耦的相关子查询，需要重点优化。
优化策略：IN vs EXISTS vs JOIN
- IN: 优化器通常会将子查询物化（Materialize）成一个临时表或哈希集合，然后与主查询进行匹配。适合子查询结果集较小的场景。
- EXISTS: 优化器执行 semi-join（半连接）。它不关心子查询返回的具体内容，只关心是否至少返回一行。一旦找到匹配行，就会停止搜索。对于子查询结果集很大，但主查询表较小的场景，或者在相关子查询中，EXISTS 通常比 IN 更高效。NOT EXISTS 是 NOT IN + NULL 陷阱的安全替代方案。
- JOIN: JOIN 是最通用和直接的方式，优化器对 JOIN 的优化策略最多（如 Nested Loop Join, Hash Join, Merge Join）。在大多数情况下，将子查询改写为 JOIN 会获得最佳性能。现代优化器（如 MySQL 8.0）会自动尝试将 IN 子查询重写为 JOIN。
查看执行计划：始终使用 EXPLAIN (或 EXPLAIN ANALYZE 在 MySQL 8.0+/PostgreSQL) 来分析子查询的执行方式。关注 type, key, rows 和 Extra 字段，确认优化器是否成功将子查询“扁平化”为高效的 JOIN，或者是否存在性能瓶颈（如 Dependent Subquery）。

常见追问与延伸题目

追问：你刚才提到了相关子查询（Correlated Subquery），它和非相关子查询有什么区别？哪个性能更好，为什么？
- 回答要点 1 (区别)：非相关子查询是独立的，可以先于主查询执行，且只执行一次。相关子查询依赖于主查询的列值，对于主查询的每一行，它都可能需要重新执行一次。
- 回答要点 2 (性能)：非相关子查询性能通常远好于相关子查询。相关子查询的朴素执行模型类似嵌套循环，其时间复杂度约等于 $O(\text{主表行数} \times \text{子查询执行代价})$ ，在大表上是灾难性的。虽然现代优化器会尝试“去关联”（de-correlation），将其转换为 JOIN，但这并非总是可行。因此，写 SQL 时应主动避免不必要的相关子查询，优先使用 JOIN 或 CTE。
追问：IN、EXISTS 和 JOIN 在实现“查询A表中存在于B表的数据”时，有什么区别和优劣？
- 回答要点 1 (语义)：JOIN 会返回匹配行的笛卡尔积（如果B表有重复，A表行会重复出现），而 IN 和 EXISTS 只做存在性判断，不会导致主表行数增加。SELECT DISTINCT a.* FROM A JOIN B ON ... 才能在结果上等价于 IN/EXISTS。
- 回答要点 2 (性能)：
  - IN: 适合子查询结果集很小的场景。MySQL 8.0 对 IN 优化很好，常会转为 semi-join。
  - EXISTS: 是 semi-join 的标准形式，通常在子查询的表很大时表现优异，因为它只要找到第一个匹配就返回 true（短路特性）。对于相关子查询，EXISTS 是首选。
  - JOIN: 如果不仅要判断存在性，还需要使用B表的列，JOIN 是不二之选。如果只是判断存在性，JOIN + DISTINCT 的开销（排序/哈希去重）可能比 EXISTS 更大。
- 总结: 没有绝对的优劣，取决于数据分布、索引情况和查询目的。经验法则是：当子查询结果集小，用 IN；当主表小、子查询表大，用 EXISTS；当两表都很大或需要子查询表的列时，用 JOIN。最终以 EXPLAIN 为准。
追问：MySQL 5.6 和 MySQL 8.0 在处理子查询时，优化器有什么显著的不同？
- 回答要点 1 (MySQL 5.6 之前)：子查询优化是 MySQL 的一个著名弱点。对于 IN 子查询，优化器倾向于“物化”（subquery materialization），即先执行子查询，将结果存入一个无索引的临时表，然后扫描这个临时表。这个过程效率低下，尤其当子查询结果集较大时。相关子查询也基本按“嵌套循环”的方式执行，性能很差。
- 回答要点 2 (MySQL 5.7/8.0)：子查询优化器得到了革命性重构。
  - 子查询展开/扁平化 (Subquery Unnesting)：优化器会智能地将大量 IN/EXISTS 子查询、派生表重写为等价的 semi-join 或 JOIN 操作，从而能够利用 JOIN 的各种高效算法和索引。
  - 物化策略改进：即使需要物化，MySQL 8.0 也会为物化后的临时表自动创建哈希索引，极大地提升了查找效率。
  - 去关联 (De-correlation)：更积极地将相关子查询转换为非相关的 JOIN。
  - 工具：MySQL 8.0 引入 EXPLAIN ANALYZE，可以显示查询的实际执行时间和行数，让开发者能更精确地看到优化器是如何执行子查询的，而不仅仅是预估。