CTE通过显式命名中间结果（如dept_stats、user_summary）使逻辑分层清晰，支持独立测试、安全重命名和强依赖顺序执行，而三层以上嵌套子查询则难以定位错误、调试成本高、易出别名冲突；但单次简单过滤（如IN/EXISTS）仍宜用子查询。

CTE命名中间结果让逻辑一目了然

子查询嵌套三层以上，SELECT 里套 SELECT 再套 SELECT，字段来源和作用完全靠猜；CTE用 WITH dept_stats AS (...) , user_summary AS (...) 显式命名，每个块干啥事、输出什么字段，扫一眼就清楚。

维护时最怕改错地方：比如把聚合字段写进 WHERE 而不是 HAVING，子查询里这种错误藏得深，CTE拆开后能直接在对应块里定位问题。

• CTE 每个分支可独立测试：复制 dept_stats 部分单独执行，验证数据是否符合预期
• 子查询无法单独运行，必须连同外层一起执行，调试成本高
• 重命名字段更安全：CTE 中用 AS 统一定义别名，避免子查询中因别名冲突或遗漏导致的 Invalid column name

子查询在简单过滤场景下反而更轻量

当只是做一层 IN 或 EXISTS 判断，比如 WHERE id IN (SELECT ref_id FROM logs WHERE type = 'error')，子查询比 CTE 更直接——少写 WITH、少起名字、不占额外语法层级。

这类场景下强行用 CTE 不仅没提升可读性，还可能误导人以为“这里有复杂逻辑”，实际只是个单表筛选。

• 子查询适合：单次使用、结果集小（
• CTE 在这种场景下无收益，还多了一层解析开销（虽微小，但积少成多）
• 注意：相关子查询（引用外部表字段）在这里极易拖慢性能，比如 WHERE t1.status = (SELECT MAX(t2.status) FROM t2 WHERE t2.group_id = t1.group_id)，这种该重构，别纠结 CTE 还是子查询

CTE的复用幻觉是个坑

很多人以为 CTE 能像临时表一样“算一次、用多次”，实际上绝大多数数据库（SQL Server、PostgreSQL 12+、MySQL 8.0 默认）不会物化 CTE。如果在主查询里两次引用 user_summary，它大概率被计算两次。

这意味着：你以为在优化结构，实际可能让性能更差——尤其当 CTE 本身含大表 JOIN 或窗口函数时。

• SQL Server 只在递归 CTE 或统计信息剧烈变化 + OPTION (RECOMPILE) 时才可能物化
• PostgreSQL 12+ 默认非物化，需显式加 MATERIALIZED（14+）或 hint
• MySQL 8.0.31+ 才支持 MATERIALIZED 提示，之前版本纯靠优化器展开
• 真正需要复用中间结果，且数据量 > 5 万行 → 直接上 #temp_table，别硬扛

递归和依赖链是CTE不可替代的场合

只有 CTE 支持递归查询，这是语法刚需。比如查组织架构下所有下属、展开评论树、遍历 BOM 物料清单，用子查询基本写不出来，或写出来是指数级嵌套，根本不可维护。

另一个真实场景：多个步骤存在强依赖，A 要用 B 的结果，B 要用 C 的结果，且每步都要聚合/过滤/去重。这时候 CTE 的顺序定义天然匹配思维流，而子查询只能靠括号堆叠，容易错位。

• 递归 CTE 必须用 UNION ALL + WHERE 限制层级，否则可能无限循环
• 非递归但有明确步骤依赖（如先清洗、再关联、最后汇总），CTE 是唯一兼顾可读与可控的写法
• 子查询在这种场景下会迅速失控：字段别名嵌套错乱、GROUP BY 列漏写、JOIN 条件漂移等问题高频出现

CTE 和子查询的维护成本差异，不在语法长短，而在“能否一眼看出数据从哪来、到哪去、中间变了什么”。一旦逻辑超过两层聚合或涉及跨步骤引用，子查询的脆弱性就会暴露——不是不能写，而是改一行，要通读五层括号才能确认影响范围。