子查詢在 WHERE、HAVING 子句中的四大測試類型：**比較測試、集合成員測試 (IN)、存在性測試 (EXISTS)、限定比較測試 (ANY/ALL)**，配合銷售資料庫實例完整示範。

場景資料模型

• OFFICES：銷售據點
• SALESREPS：業務員
• ORDERS：訂單

1. WHERE 子句中的子查詢

比較測試 (=, <>, >, <, >=, <=)

-- 列出銷售目標超過所有業務員定額總和的據點
SELECT CITY
FROM OFFICES
WHERE TARGET > (SELECT SUM(QUOTA) FROM SALESREPS WHERE REP_OFFICE = OFFICE);

-- 列出定額小於全公司目標 10% 的業務員
SELECT NAME
FROM SALESREPS
WHERE QUOTA < (0.1 * (SELECT SUM(TARGET) FROM OFFICES));

執行邏輯：外層逐列掃描，內層依外層當前列值計算標量，比較後決定保留。

集合成員測試 (IN / NOT IN)

-- 列出西部地區據點的業務員
SELECT NAME, AGE
FROM SALESREPS
WHERE REP_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE REGION = 'Western');

-- 等效 JOIN 寫法
SELECT NAME, AGE
FROM SALESREPS, OFFICES
WHERE REP_OFFICE = OFFICE AND REGION = 'Western';

存在性測試 (EXISTS / NOT EXISTS)

-- 找出有下單記錄的業務員
SELECT NAME FROM SALESREPS s
WHERE EXISTS (SELECT 1 FROM ORDERS o WHERE o.REP = s.EMPL_NUM);

-- 找出從未下單的業務員
SELECT NAME FROM SALESREPS s
WHERE NOT EXISTS (SELECT 1 FROM ORDERS o WHERE o.REP = s.EMPL_NUM);

EXISTS 優勢：不受 NULL 干擾、半連接優化 (找到即停止)、大表效能優於 IN。

限定比較測試 (ANY / ALL)

-- 定額超過平均定額的業務員
SELECT NAME, AGE
FROM SALESREPS
WHERE QUOTA > (SELECT AVG(QUOTA) FROM SALESREPS);

-- 等效 ANY (此處等價)
WHERE QUOTA > ANY (SELECT QUOTA FROM SALESREPS); -- 不同語意，僅示範語法

2. HAVING 子句中的子查詢

針對**群組**進行篩選。

範例：ACI 產品平均訂單額超過全公司平均訂單額的業務員

SELECT NAME, AVG(AMOUNT)
FROM SALESREPS, ORDERS
WHERE EMPL_NUM = REP AND MFR = 'ACI'
GROUP BY NAME
HAVING AVG(AMOUNT) > (SELECT AVG(AMOUNT) FROM ORDERS);

範例：ACI 產品平均訂單額 ≥ 該業務員全產品平均訂單額

SELECT NAME, AVG(AMOUNT)
FROM SALESREPS, ORDERS
WHERE EMPL_NUM = REP AND MFR = 'ACI'
GROUP BY NAME, EMPL_NUM
HAVING AVG(AMOUNT) >= (SELECT AVG(AMOUNT) FROM ORDERS WHERE REP = EMPL_NUM);

子查詢限制規則

• SELECT 子句只能有一個欄位 (標量子查詢)
• 不支援 ORDER BY (除非配合 TOP/LIMIT)
• 外部引用：內層可參考外層資料表欄位 (相關子查詢)
• 大多數 DB 不支援 UNION 子查詢 (單一 SELECT)

子查詢 vs JOIN 選擇指南

• 內層為匯總查詢、外層非匯總 → **無法轉 JOIN**，必須用子查詢
• 雙方均非匯總、單純關聯 → **優先 JOIN**，優化器更強
• NOT IN 有 NULL 風險 → **改用 NOT EXISTS**
• 大結果集 IN → **改用 JOIN 或 EXISTS**