子查询性能取决于执行计划而非代码长短：相关子查询每行触发内层扫描，易导致nested loops低效；临时表需建索引才能发挥优势，否则优化器误估行数；cte非递归时默认内联展开，不物化，多次引用会重复执行。

子查询在 SQL Server 中不是“写得短就一定快”，临时表也不是“写得重就一定慢”——关键看执行计划里它到底干了什么。

标量子查询在 WHERE 里反复执行？先看执行计划

SQL Server 遇到 WHERE t1.id IN (SELECT t2.ref_id FROM t2 WHERE t2.status = t1.status) 这类相关子查询时，如果外层扫描行数多、内层又没走索引，执行计划里大概率出现 Clustered Index Scan 套着 Compute Scalar，且子查询节点被标记为 Correlated。这意味着：t1 每一行，都可能触发一次 t2 的完整扫描。

• 用 SET STATISTICS XML ON 跑一遍，直接看执行计划中子查询是否被展开成嵌套循环（Nested Loops）+ 外部列引用
• 行数 > 5000 且子查询未被物化时，性能通常已明显劣于等价的 LEFT JOIN 或临时表预聚合
• 改写优先级：LEFT JOIN + GROUP BY 派生表 → SELECT INTO #tmp → 最后才考虑 CTE（除非要递归）

临时表该不该建索引？看后续怎么用

建了 #tmp 却不加索引，等于把优化器的路堵死了一半。SQL Server 对临时表的统计信息默认滞后，尤其在 INSERT INTO #tmp SELECT ... 后立刻 JOIN，常因行数误估导致哈希连接变嵌套循环，反而更慢。

• 只要 #tmp 被用于 JOIN 或 WHERE 过滤，且字段有高选择性（比如 user_id、order_date），必须加 CREATE INDEX
• 避免在单个批处理里反复 DROP TABLE #tmp; CREATE TABLE #tmp ... —— 元数据锁开销可能比计算本身还高
• 如果只是 SELECT * FROM #tmp WHERE id = @x 这种单值查找，主键或唯一约束比非聚集索引更轻量

CTE 和子查询在 SQL Server 里其实经常一模一样

SQL Server 优化器对非递归 CTE 默认做 inline 展开，和你直接把 CTE 内容抄进 FROM 子句效果一致。也就是说：WITH cte AS (SELECT ...) SELECT * FROM cte JOIN cte 很可能真跑两次底层查询，而不是“算一次、用两次”。

• 不要依赖 CTE 自动物化；想强制缓存，得加 OPTION (RECOMPILE) 或让统计信息剧烈变化（不可控）
• CTE 真正有用的地方只有两个：WITH RECURSIVE 处理树形结构，以及多个 CTE 之间存在严格依赖（A 依赖 B，B 依赖 C）且你想避免嵌套过深
• 如果只是想拆逻辑，但后续要多次引用同一结果，SELECT INTO #tmp 比 CTE 更可靠

最常被忽略的一点：SQL Server 里临时表的统计信息在首次创建后不会自动更新，除非你显式执行 UPDATE STATISTICS #tmp 或触发重编译。而子查询的执行计划一旦生成，就完全交给优化器推导——有时候它猜错了，你却不知道。