子查詢與 CTE
當查詢邏輯變複雜時，子查詢和 CTE 能幫助你拆解問題、提升可讀性。本篇將介紹各種子查詢形式，以及 PostgreSQL 強大的 CTE 功能。
一、 子查詢基礎
子查詢是嵌套在另一個查詢中的 SELECT 語句。
純量子查詢（返回單一值）
sql
-- 找出價格高於平均的產品
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- 在 SELECT 中使用
SELECT
name,
price,
(SELECT AVG(price) FROM products) AS avg_price,
price - (SELECT AVG(price) FROM products) AS diff
FROM products;列子查詢（返回一列）
sql
-- 找出有訂單的客戶
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
-- 找出沒有訂單的客戶
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);> **NOT IN 遇到 NULL 的陷阱**
如果子查詢結果包含 NULL，NOT IN 會返回空結果。務必加上 WHERE xxx IS NOT NULL。
表子查詢（返回多列多欄）
sql
-- 作為 FROM 的來源（衍生表）
SELECT category, avg_price
FROM (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
) AS category_stats
WHERE avg_price > 500;二、 EXISTS vs IN
EXISTS
檢查子查詢是否返回任何列（不關心具體內容）：
sql
-- 找出有訂單的客戶
SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);IN vs EXISTS 效能比較
| 情況 | 推薦 |
|---|---|
| 外層表大，內層表小 | IN |
| 外層表小，內層表大 | EXISTS |
| 內層有索引 | EXISTS（能利用索引） |
sql
-- 當 orders 表很大時，EXISTS 效能較好
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- 當 orders 表較小時，IN 效能較好
SELECT name FROM customers c
WHERE c.id IN (SELECT customer_id FROM orders);> **現代優化器已經很聰明**
PostgreSQL 會自動優化，大多數情況下兩者效能差異不大。優先考慮可讀性。
三、 相關子查詢
相關子查詢會引用外層查詢的欄位，每一列都會執行一次子查詢。
sql
-- 找出每個類別中價格最高的產品
SELECT p1.name, p1.category, p1.price
FROM products p1
WHERE p1.price = (
SELECT MAX(p2.price)
FROM products p2
WHERE p2.category = p1.category -- 引用外層的 p1.category
);相關子查詢 vs JOIN
相同邏輯可以用 JOIN 改寫，通常效能更好：
sql
-- 使用 JOIN 改寫
SELECT p.name, p.category, p.price
FROM products p
JOIN (
SELECT category, MAX(price) AS max_price
FROM products
GROUP BY category
) m ON p.category = m.category AND p.price = m.max_price;四、 CTE (Common Table Expression)
CTE 使用 WITH 子句定義臨時結果集，讓複雜查詢更易讀。
基本語法
sql
WITH active_customers AS (
SELECT id, name FROM customers WHERE is_active = true
),
recent_orders AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY customer_id
)
SELECT c.name, COALESCE(o.total, 0) AS total_spent
FROM active_customers c
LEFT JOIN recent_orders o ON c.id = o.customer_id
ORDER BY total_spent DESC;CTE vs 子查詢
sql
-- 使用子查詢（較難讀）
SELECT c.name, COALESCE(o.total, 0)
FROM (SELECT id, name FROM customers WHERE is_active = true) c
LEFT JOIN (
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY customer_id
) o ON c.id = o.customer_id;
-- 使用 CTE（更清晰）
WITH active_customers AS (...),
recent_orders AS (...)
SELECT ...五、 遞迴 CTE
遞迴 CTE 可以處理層級結構（如組織架構、目錄樹、路徑計算）。
語法結構
sql
WITH RECURSIVE cte_name AS (
-- 基礎情況（非遞迴部分）
SELECT ... FROM table WHERE ...
UNION ALL
-- 遞迴情況（引用自己）
SELECT ... FROM table JOIN cte_name ON ...
)
SELECT * FROM cte_name;範例：組織架構
sql
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Dev Lead', 2),
(5, 'Developer', 4),
(6, 'Junior Dev', 5);
-- 查詢 CEO 往下的所有層級
WITH RECURSIVE org_chart AS (
-- 基礎：從 CEO 開始
SELECT id, name, manager_id, 1 AS level, name::TEXT AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 遞迴：找下屬
SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ' > ' || e.name
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;結果：
| id | name | level | path |
|---|---|---|---|
| 1 | CEO | 1 | CEO |
| 3 | CFO | 2 | CEO > CFO |
| 2 | CTO | 2 | CEO > CTO |
| 4 | Dev Lead | 3 | CEO > CTO > Dev Lead |
| 5 | Developer | 4 | CEO > CTO > Dev Lead > Developer |
| 6 | Junior Dev | 5 | CEO > CTO > Dev Lead > Developer > Junior Dev |
範例：計算數列
sql
-- 費氏數列前 10 個
WITH RECURSIVE fibonacci AS (
SELECT 1 AS n, 1::BIGINT AS fib, 1::BIGINT AS prev
UNION ALL
SELECT n + 1, fib + prev, fib
FROM fibonacci
WHERE n < 10
)
SELECT n, fib FROM fibonacci;範例：路徑尋找
sql
CREATE TABLE routes (
from_city TEXT,
to_city TEXT,
distance INTEGER
);
INSERT INTO routes VALUES
('台北', '桃園', 40),
('桃園', '新竹', 50),
('新竹', '台中', 80),
('台北', '宜蘭', 60),
('宜蘭', '花蓮', 90);
-- 從台北出發可以到達的所有城市
WITH RECURSIVE reachable AS (
SELECT '台北'::TEXT AS city, 0 AS total_distance, ARRAY['台北'] AS path
UNION ALL
SELECT r.to_city, re.total_distance + r.distance, re.path || r.to_city
FROM routes r
JOIN reachable re ON r.from_city = re.city
WHERE NOT r.to_city = ANY(re.path) -- 避免循環
)
SELECT city, total_distance, path FROM reachable ORDER BY total_distance;> **避免無限遞迴**
遞迴 CTE 必須有終止條件。使用 WHERE 子句或追蹤已訪問節點來防止無限循環。
六、 CTE 物化行為
PostgreSQL 12+ 可以控制 CTE 是否物化（先執行並儲存結果）：
sql
-- 強制物化（預設行為）
WITH cte AS MATERIALIZED (
SELECT * FROM big_table WHERE ...
)
SELECT * FROM cte WHERE ...;
-- 不物化（視為內嵌子查詢）
WITH cte AS NOT MATERIALIZED (
SELECT * FROM big_table WHERE ...
)
SELECT * FROM cte WHERE ...;| 模式 | 優點 | 缺點 |
|---|---|---|
| MATERIALIZED | 只執行一次，多次引用時快 | 無法下推過濾條件 |
| NOT MATERIALIZED | 可下推過濾條件 | 多次引用會重複執行 |
七、 進階 CTE 技巧
CTE 中的 INSERT/UPDATE/DELETE
sql
-- 刪除舊資料並返回刪除的行
WITH deleted AS (
DELETE FROM orders
WHERE created_at < NOW() - INTERVAL '1 year'
RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;鏈式 CTE
sql
WITH
step1 AS (
SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
),
step2 AS (
SELECT customer_id, total,
NTILE(4) OVER (ORDER BY total) AS quartile
FROM step1
),
step3 AS (
SELECT * FROM step2 WHERE quartile = 4 -- 前 25% 客戶
)
SELECT c.name, s.total
FROM step3 s
JOIN customers c ON s.customer_id = c.id;八、 何時用子查詢，何時用 CTE？
| 情況 | 推薦 |
|---|---|
| 簡單的 IN/EXISTS 檢查 | 子查詢 |
| 需要多次引用同一結果 | CTE |
| 複雜的多步驟邏輯 | CTE |
| 遞迴處理層級資料 | 遞迴 CTE |
| 需要在 DML 中使用 | CTE |
總結
| 概念 | 用途 |
|---|---|
| 純量子查詢 | 返回單一值，用於比較 |
| IN / NOT IN | 檢查值是否在集合中 |
| EXISTS | 檢查子查詢是否有結果 |
| 相關子查詢 | 引用外層查詢，每列執行一次 |
| CTE | 命名的臨時結果集，提升可讀性 |
| 遞迴 CTE | 處理層級結構、路徑計算 |
進階挑戰
- 使用遞迴 CTE 計算「從員工 A 到員工 B 的最短管理路徑」
- 將一個複雜的多層嵌套子查詢改寫成 CTE，比較可讀性
- 使用 CTE + DELETE RETURNING 實作「刪除訂單並同時歸檔」的功能