SQL中的Subquery & CTE & Temporary Table 区别

Subquery、CTE 和 Temporary Table 的深度对比

这三个技术都用于创建临时数据集，但它们在实现方式、使用场景和性能特点上有显著差异。让我们用"数学演草纸"的比喻来深入分析：

1. 子查询 (Subquery)

比喻：就像在解题过程中随手在题目旁边写的草稿计算

特点：

• 内联性：直接嵌套在SQL语句中（SELECT/FROM/WHERE等子句内）
• 一次性使用：定义后只能在该处使用一次
• 无命名：通常没有显式的名称（除非是派生表）
• 作用域：仅在包含它的查询中有效

示例：

SELECT user_id 
FROM orders 
WHERE amount > (SELECT AVG(amount) FROM orders);  -- WHERE子句中的子查询

适用场景：

• 简单的单次使用计算
• 作为过滤条件或比较值
• 快速测试不需要复用的逻辑

2. 公共表表达式 (CTE, Common Table Expression)

比喻：专门拿出一张草稿纸写中间步骤，可以随时翻看

特点：

• 显式命名：使用WITH cte_name AS语法定义
• 可复用性：在同一个WITH子句中可定义多个CTE，且后面的CTE可以引用前面的
• 查询级作用域：仅在紧随其后的单个SQL语句中有效
• 可递归：支持递归查询（处理层次结构数据）

示例：

-- 使用CTE和比较运算符 >，先筛选出消费总额超过1000的高价值用户和最近30天活跃用户，最终通过 INTERSECT 取两者的交集，找出​​既高消费又活跃的核心用户群体​​。
WITH high_value_customers AS (
    SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 1000
), #筛选订单总额超过1000的用户
active_customers AS (
    SELECT user_id FROM logins WHERE login_date > CURRENT_DATE - 30
) #筛选30天内有登录记录的活跃用户
SELECT * FROM high_value_customers 
INTERSECT 
SELECT * FROM active_customers;

适用场景：

• 复杂查询的模块化设计
• 需要多次引用同一结果集
• 递归查询
• 提高复杂查询的可读性

3. 临时表 (Temporary Table)

比喻：专门准备一个笔记本记录中间结果，可以反复翻阅和修改

特点：

• 物理存储：实际存储在tempdb中（内存或磁盘）
• 会话级作用域：创建后在整个会话期间可用，直到显式删除或会话结束
• 可索引：可以添加索引优化性能
• 可修改：支持INSERT/UPDATE/DELETE操作
• 跨查询使用：可以被同一会话的多个查询使用

示例：

CREATE TEMPORARY TABLE temp_high_value AS
SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 1000;

ALTER TABLE temp_high_value ADD INDEX (user_id);  -- 可以添加索引

SELECT * FROM temp_high_value h JOIN users u ON h.user_id = u.id;

DROP TEMPORARY TABLE IF EXISTS temp_high_value;  -- 显式清理

适用场景：

• 复杂的ETL流程
• 需要多次重用的中间结果
• 大型数据集处理（特别是需要索引优化时）
• 跨多个SQL语句共享数据

三者的核心对比

特性	子查询 (Subquery)	CTE	临时表 (Temporary Table)
存储方式	逻辑存在，不物理存储	逻辑存在，可能被优化器物化	物理存储在tempdb
作用域	当前子句	当前语句	整个会话
生命周期	查询执行期间	查询执行期间	显式删除或会话结束
是否可复用	不可复用	同一WITH子句内可引用	跨查询复用
是否可修改	不可修改	不可修改	可INSERT/UPDATE/DELETE
是否支持索引	不支持	不支持	支持
性能特点	简单查询高效	中等复杂度查询最优	复杂数据处理最优
语法复杂度	简单	中等	较高
典型使用场景	简单过滤/计算	复杂查询模块化	跨语句共享数据/大型处理

如何选择？

1. 简单计算 → 子查询
2. 中等复杂度查询 → CTE（提高可读性）
3. 需要多次引用/修改 → 临时表
4. 递归查询 → CTE WITH RECURSIVE
5. 会话级重用 → 临时表

记住：随着SQL复杂度的增加，通常的开发路径是：子查询 → CTE → 临时表。优化器对三者的处理方式不同，在性能关键场景中需要测试验证。