SQL优化器自动展开子查询是为了避免相关子查询每行重复执行导致的指数级性能恶化；其本质是通过重写为JOIN或物化实现去相关化，但是否展开取决于成本估算，且必须确保语义等价。
SQL优化器自动展开子查询，不是为了“炫技”，而是因为不展开就容易卡死——尤其当子查询每行都重跑一次时，性能会指数级恶化。
相关子查询不展开 = 每行执行一次
只要子查询里引用了外层表的列（比如 WHERE o.user_id = u.id），它就是相关子查询。优化器无法预计算结果，只能在外层扫描的每一行上重复执行它。
- 外层表 10 万行 → 子查询被执行 10 万次
- 哪怕子查询加了索引，也是 10 万次单点查找，I/O 和 CPU 开销爆炸
- EXPLAIN 中看到
dependent subquery（MySQL）或SubPlan（PostgreSQL），就是这个信号
展开的本质是“去相关化”
优化器把相关子查询重写成 JOIN 或物化临时表，核心目标是切断“每行触发一次”的依赖链。
- IN/EXISTS 类子查询常转为
Semi Join或Hash Match，一次性加载右表，复杂度从 O(N×M) 降到 O(N+M) - 标量子查询（如
(SELECT AVG(sales) FROM s WHERE s.user_id = u.id)）在 Oracle 12c+、SQL Server 2016+ 可转为GROUP BY+ 外连接，只扫一遍大表 - MySQL 需显式开启：
SET optimizer_switch='semijoin=on,materialization=on'，否则默认可能退化为嵌套循环
为什么有时不展开？成本估算说了算
展开不是必然动作，优化器会权衡：展开带来的内存/临时表开销，是否真比硬扛嵌套循环更便宜。
- 子查询结果极小（比如只返回 1 行）、外层表无索引 → 优化器可能觉得
Nested Loops更省事 - 子查询含
LIMIT、ORDER BY、窗口函数、聚合 → 几乎必然被物化，但物化后若落盘且无索引，反而更慢 - CTE 默认强制物化（PostgreSQL/SQL Server），哪怕只用一次；而普通子查询更倾向内联，除非带排序或分组
手动展开前必须核对三件事
别光看执行计划变快了就以为改对了——语义漂移比性能差更致命。
-
IN对 NULL 敏感，INNER JOIN天然过滤 NULL；若子查询列允许 NULL，直接替换会丢数据 - 原写法是
WHERE dept_id = (SELECT TOP 1 id FROM dept ...)，手动 JOIN 必须加DISTINCT或ROW_NUMBER()，否则一对多放大行数 - 原
WHERE条件塞进ON还是留在外层WHERE，在LEFT JOIN下结果完全不同——前者影响关联逻辑，后者只过滤最终结果
真正难的不是“能不能展开”，而是展开后是否还等于原来的语义。优化器不会替你担这个责任，它只负责算得快，不负责算得对。
