普通嵌套子查询适合单次、简单过滤场景，如单层WHERE条件或标量计算；派生表是匿名临时结果集，仅限当前FROM子句使用；CTE是命名逻辑块，支持多次引用与递归，适用于复用≥2次、嵌套≥3层或需业务命名的复杂逻辑。
别硬套“高级语法”，先看它是不是真需要被拆出来——普通嵌套子查询在单次、简单过滤时最轻量；派生表和CTE不是替代品，而是不同协作阶段的工具。
什么时候该用普通嵌套子查询
它适合“查完就扔”的一次性逻辑，比如单层 WHERE 条件或 SELECT 列里的标量计算。优化器对这类结构处理得最成熟，写法也最直接。
- 典型场景：
WHERE salary > (SELECT AVG(salary) FROM employees)，或SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_cnt FROM users u - 别强改：如果只出现一次，且没嵌套超过两层，改成
WITH反而多写名字、多占行、增加阅读负担 - 危险信号：子查询里引用了外层字段（即相关子查询），比如
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id)—— 这种性能极差，应优先改用LEFT JOIN + GROUP BY或窗口函数
派生表（FROM 中的子查询）和 CTE 本质区别在哪
派生表是匿名的、强制带别名的临时结果集，只在当前 FROM 子句中存在；CTE 是命名的、可多次引用的逻辑块，作用域覆盖整个主查询。它们不是语法糖替换关系，而是语义粒度不同。
- 派生表必须有别名，例如：
FROM (SELECT user_id, SUM(amount) FROM orders GROUP BY user_id) AS user_orders - CTE 可省略别名但必须命名，例如：
WITH user_orders AS (SELECT user_id, SUM(amount) ...) - CTE 支持递归（
WITH RECURSIVE），派生表完全不支持 - 调试友好性差异大：你能直接
SELECT * FROM user_orders验证 CTE，但没法单独跑一个派生表——它没名字，也没独立作用域
CTE 真正值得用的三个硬条件
CTE 不是“看起来更高级”就该上，它的价值只在满足以下任一条件时才明确大于子查询或派生表：
- 同一逻辑在主查询中被引用 ≥2 次，比如既用于
JOIN又用于WHERE EXISTS，此时 CTE 保证语义一致，避免手误漏改某一处 - 嵌套深度 ≥3 层，例如
FROM (SELECT ... FROM (SELECT ... FROM (SELECT ...)))，缩进已超出屏幕宽度，可读性崩溃 - 中间结果含聚合+JOIN+复杂过滤，且能用业务名表达意图，如
active_users、q3_refunded_orders—— 名字本身就在传递逻辑，不是为了命名而命名
临时表才是 CTE 的“后备选项”，尤其当性能敏感时
CTE 在 PostgreSQL 默认物化，但在 MySQL 8.0+ 和 SQL Server 中可能被优化器内联展开，导致耗时 CTE 被重复执行多次。这时候临时表反而更稳。
- 典型信号：CTE 中扫描了千万级表，又被主查询引用两次以上，EXPLAIN 显示相同扫描出现多次
- 临时表写法：
CREATE TEMP TABLE tmp_orders AS SELECT ...，后续可反复JOIN、加索引、甚至ANALYZE - 注意权限与生命周期：临时表只在当前会话有效，且某些环境（如只读从库、无
CREATE TEMP TABLE权限）无法使用 - 别忽略成本：建临时表有 IO 开销，小数据量下反而比 CTE 慢；只有当“重复计算代价 > 写入+读取代价”时才划算
真正容易被忽略的是数据库行为差异：同一个 WITH 语句，在 PostgreSQL 里可能只算一次，在 MySQL 里却执行三次。上线前务必用 EXPLAIN ANALYZE 看真实执行计划，而不是依赖“CTE 就是物化”这种笼统说法。
