cte命名后能单独执行，子查询不能；cte支持多处引用但默认不物化，可能性能更差；三层以上嵌套子查询难以维护，而简单过滤场景下子查询更轻量直接。
CTE命名后能单独执行，子查询不能
CTE用WITH定义的块是可独立运行的逻辑单元。比如你写了WITH user_orders AS (SELECT user_id, COUNT(*) FROM orders GROUP BY user_id)，可以直接复制SELECT * FROM user_orders去验证结果是否符合预期。子查询不行——它嵌在WHERE、FROM或SELECT里，脱离上下文就语法报错，调试时只能靠猜字段来源或硬着头皮全查一遍。
CTE支持多处引用但不自动物化，性能可能更差
很多人以为WITH cte AS (SELECT ...)之后在主查询里引用两次，数据库只会算一次。事实相反：PostgreSQL 12+默认非物化，MySQL 8.0.31之前完全不物化，SQL Server只在递归或加OPTION (RECOMPILE)时才可能物化。这意味着：
-
SELECT * FROM cte t1 JOIN cte t2 ON t1.id = t2.parent_id很可能触发两次底层扫描 - 如果CTE本身含大表
JOIN或窗口函数，性能反而比等效子查询还低 - 真要复用且数据量 > 5 万行，该用
#temp_table而不是硬扛CTE
嵌套三层以上子查询基本等于放弃维护
当你看到类似WHERE id IN (SELECT id FROM (SELECT id FROM (SELECT id FROM logs WHERE type = 'error') t1) t2)这种写法，说明逻辑已经失控。问题不止是难读：
- 字段别名容易冲突，
Invalid column name错误定位困难 - 聚合字段误写进
WHERE而非HAVING，错误藏在第二层括号里 - 同一计算逻辑（如用户首单日期）在
SELECT和WHERE里各写一遍，改一处漏一处
这时候用CTE不是为了“高级”，而是把first_order AS (SELECT user_id, MIN(order_date) ...)拆出来，让每个环节职责单一。
简单过滤场景下子查询更直接
像WHERE status IN (SELECT status FROM config WHERE active = 1)或WHERE amount > (SELECT AVG(amount) FROM sales)这类单次标量/集合判断，子查询更轻量：
- 少写
WITH、不用起名、不增加语法层级 - 优化器对这类半连接处理成熟，执行计划通常比CTE更优
- 强行套CTE反而误导读者以为有复杂中间逻辑
真正需要警惕的是相关子查询——比如WHERE t1.id = (SELECT MAX(t2.id) FROM t2 WHERE t2.group_id = t1.group_id)，这种必须重构为JOIN或窗口函数，跟CTE或子查询选型无关。
