子查询的原理与深度解析
子查询，又称嵌套查询，是SQL语言中一个强大且核心的功能。它允许将一个完整的SELECT查询语句嵌套在另一个查询语句（如SELECT, INSERT, UPDATE, DELETE）的WHERE、HAVING、FROM子句或SELECT列表中，作为其查询条件或数据源的一部分。
一、 子查询的核心原理与执行机制
子查询的执行并非简单的“由内而外”，其具体行为取决于子查询的类型以及数据库优化器的决策。
1. 子查询的基本分类
根据子查询与外部查询的依赖关系，主要分为两大类：
| 不相关子查询 | 子查询可以独立执行，其结果不依赖于外部查询的每一行。 | 通常先执行子查询，将结果集（可能是一个值或一个列表）物化，然后作为常量或集合提供给外部查询使用。 | 性能相对较好，但若子查询结果集很大，物化过程会消耗内存和I/O。 |
| 相关子查询 | 子查询的执行依赖于外部查询的当前行。子查询中引用了外部查询表的列。 | 对于外部查询的每一行候选数据，都要执行一次子查询来验证条件。 | 极易成为性能瓶颈。假设外部查询有N行，子查询复杂度为M，则总复杂度接近O(N*M)。 |
根据返回结果的形式，子查询还可以细分为：
- 标量子查询：返回单个值（一行一列）。
- 行子查询：返回单行（一行多列）。
- 列子查询：返回单列（多行一列），常与 IN、ANY、ALL 等操作符联用。
- 表子查询：返回一个多行多列的派生表，通常用于FROM子句。
2. 现代数据库优化器的重写与优化
为了提高性能，现代数据库（如MySQL 8.0+, OceanBase）的查询优化器会尝试将低效的子查询重写为更高效的连接（JOIN）操作。
-
子查询转连接（Semi-Join / Anti-Join）：这是最核心的优化之一。例如，一个使用 IN 或 EXISTS 的子查询，优化器可能将其重写为半连接（Semi-Join），其含义是“只要在子查询中找到至少一个匹配行，就返回外部查询的行，且不会导致结果行数因匹配而翻倍”。NOT IN 或 NOT EXISTS 可能被重写为反连接（Anti-Join）。OceanBase明确提供了where子查询提升规则来处理这类转换。
— 原查询（可能被优化）
SELECT * FROM employees e
WHERE e.dept_id IN (SELECT id FROM departments WHERE location = 'HQ');— 优化器可能内部重写为类似以下的半连接
SELECT e.* FROM employees e
SEMI JOIN departments d ON e.dept_id = d.id AND d.location = 'HQ'; -
聚合子查询提升：对于包含聚合函数（如MAX, MIN, SUM）的子查询，优化器（如OceanBase的聚合子查询提升规则）可能将其转换为一个带有GROUP BY的派生表，然后与主查询进行连接，从而可以利用连接算法和索引进行优化。
-
物化（Materialization）：对于复杂的不相关子查询，优化器可能选择将其结果集预先计算并存储在一个临时表（物化表）中，后续查询直接使用该临时表，避免重复执行子查询。
二、 经典例题解析
我们使用经典的学生-课程-成绩数据库模型进行例题演示。
- 学生表 S(S#, SNAME, AGE, SEX)
- 课程表 C(C#, CNAME, TEACHER)
- 选课表 SC(S#, C#, GRADE)
例题1：查询选修了“程军”老师所授全部课程的学生姓名。
这是一个典型的“除”操作逻辑。思路是：不存在一门“程军”老师教的课，是这个学生没选的。
— 方法1：使用 NOT EXISTS + 双重嵌套子查询（相关子查询）
SELECT SNAME FROM S s
WHERE NOT EXISTS (
— 找出程军老师教的所有课
SELECT * FROM C c1 WHERE c1.TEACHER = '程军'
AND NOT EXISTS (
— 检查当前学生s是否选了这门课c1
SELECT * FROM SC WHERE S# = s.S# AND C# = c1.C#
)
);
原理分析：这是一个相关子查询。对于S表中的每一个学生s，都要执行一次外部NOT EXISTS的子查询。该子查询枚举所有程军老师的课程c1，并检查是否存在该学生没选的课（内部NOT EXISTS）。只有当一个学生不存在任何一门程军老师的课没选时，他才满足条件。虽然逻辑清晰，但嵌套层级深，且是相关子查询，在数据量大时性能需谨慎评估。
例题2：查询没有选修任何课程的学生的学号和姓名。
— 方法1：使用 NOT IN 子查询
SELECT S#, SNAME FROM S
WHERE S# NOT IN (SELECT DISTINCT S# FROM SC WHERE S# IS NOT NULL); — 注意处理NULL值
— 方法2：使用 NOT EXISTS 子查询（通常更优，能正确处理NULL）
SELECT S#, SNAME FROM S s
WHERE NOT EXISTS (SELECT 1 FROM SC WHERE S# = s.S#);
原理对比：NOT IN子查询是不相关子查询，会先执行内部查询得到一个学号列表。但当列表中存在NULL值时，NOT IN的结果可能永远为FALSE或NULL，导致查询结果异常，需要显式排除NULL。NOT EXISTS是相关子查询，但现代优化器很可能将其提升为反连接（Anti-Join），性能往往更好，且语义上天然正确处理NULL。
例题3：查询每门课程成绩高于该课程平均分的学生学号、课程号和成绩。
这是一个典型的需要在子查询中使用外部查询列的案例。
SELECT sc1.S#, sc1.C#, sc1.GRADE
FROM SC sc1
WHERE sc1.GRADE > (
SELECT AVG(sc2.GRADE)
FROM SC sc2
WHERE sc2.C# = sc1.C# — 子查询关联了外部查询的课程号
GROUP BY sc2.C#
);
原理分析：这是一个相关标量子查询。对于SC表中的每一行记录sc1，数据库都需要执行一次子查询，来计算sc1所在课程（sc1.C#）的平均成绩。这种“逐行计算”的模式是相关子查询性能差的主要原因。对于这种自关联的聚合查询，有时重写为窗口函数或与聚合派生表连接会效率更高。
三、 性能陷阱与优化策略
警惕相关子查询：如例题3所示，它可能导致O(N*M)的复杂度。优化策略：尽可能将其重写为JOIN。
— 例题3的JOIN优化写法
SELECT sc1.S#, sc1.C#, sc1.GRADE
FROM SC sc1
JOIN (SELECT C#, AVG(GRADE) as avg_grade FROM SC GROUP BY C#) course_avg
ON sc1.C# = course_avg.C#
WHERE sc1.GRADE > course_avg.avg_grade;
改写后，子查询（派生表course_avg）只执行一次并物化，然后与主表进行高效的连接操作，可以充分利用C#上的索引。
慎用 IN 与 NOT IN：特别是当子查询结果集很大时。IN子查询可能被优化为半连接，而NOT IN在存在NULL时有语义风险且可能无法有效优化。优先考虑使用 EXISTS / NOT EXISTS 或 LEFT JOIN … IS NULL 的写法。
利用EXPLAIN分析：使用EXPLAIN命令查看执行计划，是判断子查询是否被优化、使用了何种连接算法（如Hash Join, Nested Loop）的关键手段。观察是否有“DEPENDENT SUBQUERY”（相关子查询）字样，这通常是性能警示信号。
索引是优化的基石：确保子查询关联字段（如SC表的S#, C#）和WHERE条件字段上建有合适的索引，能极大提升子查询（尤其是相关子查询）和连接操作的性能。
总结：理解子查询的原理，关键在于区分相关与不相关，并知晓优化器会尝试进行子查询转连接的改写。在编写SQL时，应优先考虑使用JOIN进行逻辑表达，若必须使用子查询，应避免深层的相关子查询，并时刻通过执行计划来验证其效率。对于复杂的“全部/任何”逻辑（如例题1），虽然子查询表达直观，但也应评估在具体数据规模下的性能表现。
参考来源
- 【三桥君】设有一个数据库中有三个关系： 学生关系S（S#，SNAME，AGE，SEX) 课程关系C（C#，CNAME, TEACHER) 学习关系SC（S#，C#， GRADE）
- where子查询提升规则原理与代码解析 | OceanBase查询优化
- 聚合子查询提升的原理与解析 | OceanBase查询优化
- MySQL查询之子查询详解：从执行原理到深度优化
- MySQL JOIN 与子查询深度对比：原理、性能陷阱与优化策略
- 深度整理总结MySQL——子查询的工作原理
