CTE vs 子查詢：什麼時候該選哪個？

我們已經知道 CTE 讓程式碼更好讀。但是不是每次都該用 CTE 呢？有時候簡單的子查詢反而更快更有效率。來看看什麼情況下該用哪個工具，學會怎麼做出明智的選擇。

子查詢：快速又簡單

你應該記得，子查詢就是 SQL 裡面再包一個 SQL。它直接嵌在主查詢裡，當場執行。超適合一次性的簡單操作：

-- 找出價格高於平均值的商品
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

這裡子查詢只算一次平均價格，搞定就收工。沒有多餘的結構。

效能：誰比較快？

子查詢 在簡單操作時通常比較快。PostgreSQL 可以「即時」優化，尤其是子查詢只執行一次時：

-- 快速：子查詢只執行一次
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

CTE 預設會被 materialize —— PostgreSQL 會先算出 CTE 結果，存成暫存表，再拿來用。這會拖慢簡單查詢：

-- 比較慢：CTE 會 materialize 成暫存表
WITH latest_date AS (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

不過！從 PostgreSQL 12 開始你可以控制 materialization：

-- 強制不要 materialize
WITH latest_date AS NOT MATERIALIZED (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

多次使用：這裡 CTE 超強

當你需要多次用到同一個中間結果時，CTE 就變得不可或缺：

-- 用子查詢：同一邏輯寫兩次
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = '已完成') AS 已完成訂單數,
    (SELECT COUNT(*) FROM orders WHERE status = '已完成') * 100.0 / COUNT(*) AS 完成率
FROM orders;

-- 用 CTE：算一次，用兩次
WITH 已完成訂單 AS (
    SELECT COUNT(*) AS 數量 FROM orders WHERE status = '已完成'
)
SELECT
    co.數量 AS 已完成訂單數,
    co.數量 * 100.0 / (SELECT COUNT(*) FROM orders) AS 完成率
FROM 已完成訂單 co;

複雜分析：CTE 完勝

多步驟分析時，CTE 可以把混亂變成有條理。來比較一下銷售報表：

用子查詢（腦袋打結）：

SELECT 
    category,
    revenue,
    revenue * 100.0 / (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE EXTRACT(year FROM o.order_date) = 2024
    ) AS 營收佔比
FROM (
    SELECT 
        p.category,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
    GROUP BY p.category
) category_revenue;

用 CTE（超有條理）：

WITH 年度銷售 AS (
    SELECT 
        p.category,
        p.price * oi.quantity AS 銷售金額
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
),
分類營收 AS (
    SELECT 
        category,
        SUM(銷售金額) AS revenue
    FROM 年度銷售
    GROUP BY category
),
總營收 AS (
    SELECT SUM(銷售金額) AS total FROM 年度銷售
)
SELECT 
    cr.category,
    cr.revenue,
    cr.revenue * 100.0 / tr.total AS 營收佔比
FROM 分類營收 cr, 總營收 tr;

遞迴：CTE 壟斷

遇到階層結構，子查詢完全沒轍。

只有遞迴 CTE 能搞定像「找出某個經理所有下屬」這種需求：

WITH RECURSIVE 員工階層 AS (
    -- 從 CEO 開始
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 加入每一層的下屬
    SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
    FROM employees e
    JOIN 員工階層 eh ON e.manager_id = eh.employee_id
)
SELECT * FROM 員工階層 ORDER BY level, name;

除錯與維護程式碼

CTE 可以分段除錯超方便：

-- 先檢查第一步
WITH 活躍客戶 AS (
    SELECT customer_id FROM customers WHERE status = '活躍'
)
SELECT COUNT(*) FROM 活躍客戶; -- 確認邏輯正確

-- 加入第二步
WITH 活躍客戶 AS (...),
近期訂單 AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM 近期訂單; -- 也檢查這一步

子查詢 除錯比較麻煩——你得把它們抽出來單獨測。

實用建議

什麼時候用子查詢：

• 邏輯簡單，一行就能搞定
• 要追求簡單操作的極致效能
• 中間結果只用一次
• 資料量不大

什麼時候用 CTE：

• 查詢很複雜，可以拆成多個邏輯步驟
• 中間結果要用很多次
• 重視程式碼可讀性跟維護性
• 處理階層結構（遞迴 CTE）
• 要分段除錯複雜邏輯

黃金法則

先從子查詢開始。如果發現越來越難讀，或邏輯重複，就換成 CTE。你未來的同事（或半年後的你自己）一定會感謝你！