SQL 子查詢：標量子查詢 vs 派生表的區別與應用

兩種子查詢形式對比

1. 標量子查詢（作為列表達式）

SELECT 
    column1,
    (SELECT aggregate_col FROM table2 WHERE table2.id = table1.id) AS derived_col
FROM table1;

• 子查詢出現在 SELECT 列表中
• 必須返回單行單列（標量值）
• 可引用外層查詢的列（相關子查詢）
• 適用於：為每行計算聚合值、狀態標記等

2. 派生表/內聯視圖（作為表來源）

SELECT d.column1, d.derived_col
FROM (
    SELECT id, aggregate_col AS derived_col
    FROM table2
    WHERE condition
) AS d
JOIN table1 t ON t.id = d.id;

• 子查詢出現在 FROM 子句中
• 返回結果集（多行多列），視為臨時表
• 必須起別名（如 AS d）
• 外層查詢通過別名引用其列（d.column）
• 適用於：複雜過濾後的中間結果集、多步聚合、橫向分表查詢

核心差異總結

什麼是臨時表？

此處「臨時表」指派生表產生的邏輯中間結果集，而非物理臨時表（CREATE TEMPORARY TABLE）。它：

• 僅在當前查詢執行期間存在
• 不佔用持久存儲
• 優化器可能將其合併到外層查詢執行計劃中

為什麼不直接寫表名/列名？

• 邏輯封裝：將複雜過濾/聚合邏輯隔離，主查詢更清晰
• 數據轉換：派生表可預先計算、轉換數據格式
• 跨表聚合：先在子查詢中聚合，再與主表關聯，避免重複分組

實戰示例

查詢每個部門最高薪資的員工：

-- 派生表方案（推薦）
SELECT e.name, e.salary, d.dept_name
FROM employee e
JOIN (
    SELECT dept_id, MAX(salary) AS max_sal
    FROM employee
    GROUP BY dept_id
) AS dm ON e.dept_id = dm.dept_id AND e.salary = dm.max_sal
JOIN department d ON e.dept_id = d.id;

-- 標量子查詢方案（效率較低）
SELECT e.name, e.salary, d.dept_name
FROM employee e
JOIN department d ON e.dept_id = d.id
WHERE e.salary = (
    SELECT MAX(salary) FROM employee WHERE dept_id = e.dept_id
);

總結

• 單值計算 → 標量子查詢
• 中間結果集/複雜邏輯隔離 → 派生表
• 現代優化器對派生表優化更好，優先考慮 FROM (subquery) AS alias 模式