Subquery、CTE 和 Temporary Table 的深度對(duì)比

這三個(gè)技術(shù)都用于創(chuàng)建臨時(shí)數(shù)據(jù)集，但它們?cè)趯?shí)現(xiàn)方式、使用場(chǎng)景和性能特點(diǎn)上有顯著差異。讓我們用"數(shù)學(xué)演草紙"的比喻來(lái)深入分析：

1. 子查詢 (Subquery)

比喻：就像在解題過程中隨手在題目旁邊寫的草稿計(jì)算

特點(diǎn)：

• 內(nèi)聯(lián)性：直接嵌套在SQL語(yǔ)句中（SELECT/FROM/WHERE等子句內(nèi)）
• 一次性使用：定義后只能在該處使用一次
• 無(wú)命名：通常沒有顯式的名稱（除非是派生表）
• 作用域：僅在包含它的查詢中有效

示例：

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

適用場(chǎng)景：

• 簡(jiǎn)單的單次使用計(jì)算
• 作為過濾條件或比較值
• 快速測(cè)試不需要復(fù)用的邏輯

2. 公共表表達(dá)式 (CTE, Common Table Expression)

比喻：專門拿出一張草稿紙寫中間步驟，可以隨時(shí)翻看

特點(diǎn)：

• 顯式命名：使用WITH cte_name AS語(yǔ)法定義
• 可復(fù)用性：在同一個(gè)WITH子句中可定義多個(gè)CTE，且后面的CTE可以引用前面的
• 查詢級(jí)作用域：僅在緊隨其后的單個(gè)SQL語(yǔ)句中有效
• 可遞歸：支持遞歸查詢（處理層次結(jié)構(gòu)數(shù)據(jù)）

示例：

-- 使用CTE和比較運(yùn)算符 >，先篩選出消費(fèi)總額超過1000的高價(jià)值用戶和最近30天活躍用戶，最終通過 INTERSECT 取兩者的交集，找出??既高消費(fèi)又活躍的核心用戶群體??。
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天內(nèi)有登錄記錄的活躍用戶
SELECT * FROM high_value_customers 
INTERSECT 
SELECT * FROM active_customers;

適用場(chǎng)景：

• 復(fù)雜查詢的模塊化設(shè)計(jì)
• 需要多次引用同一結(jié)果集
• 遞歸查詢
• 提高復(fù)雜查詢的可讀性

3. 臨時(shí)表 (Temporary Table)

比喻：專門準(zhǔn)備一個(gè)筆記本記錄中間結(jié)果，可以反復(fù)翻閱和修改

特點(diǎn)：

• 物理存儲(chǔ)：實(shí)際存儲(chǔ)在tempdb中（內(nèi)存或磁盤）
• 會(huì)話級(jí)作用域：創(chuàng)建后在整個(gè)會(huì)話期間可用，直到顯式刪除或會(huì)話結(jié)束
• 可索引：可以添加索引優(yōu)化性能
• 可修改：支持INSERT/UPDATE/DELETE操作
• 跨查詢使用：可以被同一會(huì)話的多個(gè)查詢使用

示例：

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;  -- 顯式清理

適用場(chǎng)景：

• 復(fù)雜的ETL流程
• 需要多次重用的中間結(jié)果
• 大型數(shù)據(jù)集處理（特別是需要索引優(yōu)化時(shí)）
• 跨多個(gè)SQL語(yǔ)句共享數(shù)據(jù)

三者的核心對(duì)比

如何選擇？

• 簡(jiǎn)單計(jì)算 → 子查詢
• 中等復(fù)雜度查詢 → CTE（提高可讀性）
• 需要多次引用/修改 → 臨時(shí)表
• 遞歸查詢 → CTE WITH RECURSIVE
• 會(huì)話級(jí)重用 → 臨時(shí)表

記?。弘S著SQL復(fù)雜度的增加，通常的開發(fā)路徑是：子查詢 → CTE → 臨時(shí)表。優(yōu)化器對(duì)三者的處理方式不同，在性能關(guān)鍵場(chǎng)景中需要測(cè)試驗(yàn)證。

到此這篇關(guān)于SQL中的Subquery & CTE & Temporary Table 區(qū)別深度對(duì)比分析的文章就介紹到這了,更多相關(guān)sql subquery、CTE 和 temporary table內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論