SQL Server 子查詢完整分類與實戰範例：比較、IN、EXISTS 三大類型

子查詢三大語法類別

1. 比較運算子 + 子查詢 (ANY / ALL)

-- 任一滿足 (ANY)
SELECT sale_id, tot_amt 
FROM sales 
WHERE tot_amt > ANY (SELECT tot_amt FROM sales WHERE sale_id='e0013' AND order_date='1996/11/10');

-- 全部滿足 (ALL)
SELECT * FROM sales a
WHERE tot_amt = (SELECT MAX(tot_amt) FROM sales WHERE name = a.name);  -- 各人最高額訂單

• ANY：子查詢結果集中**任一**值滿足即真
• ALL：子查詢結果集中**所有**值滿足才真
• = ANY 等同 IN；<> ALL 等同 NOT IN

2. IN / NOT IN 子查詢

-- 女性業務員的銷售單
SELECT sales_id, tot_amt 
FROM sales 
WHERE sale_id IN (SELECT sale_id FROM employee WHERE sex = 'F');

• 子查詢回傳單欄位值清單
• 注意：子查詢結果含 NULL 時，NOT IN 整體判斷為 UNKNOWN (無回傳)，建議改用 NOT EXISTS

3. EXISTS / NOT EXISTS — 關聯子查詢 (效能通常最佳)

-- 滯銷產品：庫存表中不存在任何銷售明細記錄
SELECT * FROM stock a
WHERE NOT EXISTS (
    SELECT * FROM sale_item b 
    WHERE a.prod_id = b.prod_id AND a.stup_id = b.stup_id
);

-- 業務員累計銷售 < 20萬
SELECT emp_no, emp_name 
FROM employee a
WHERE (SELECT SUM(tot_amt) FROM sales b WHERE a.sale_id = b.emp_no) < 200000;

• EXISTS：子查詢**至少一列**存在即真，**不實際回傳資料**，只判斷存在性
• 優化器常轉為 Semi-Join，避免重複掃描，大表效能優於 IN
• 關聯條件在子查詢 WHERE 中 (如 a.prod_id = b.prod_id)

效能與選擇建議

現代最佳實踐：優先使用 EXISTS/NOT EXISTS，搭配適當索引 (子查詢關聯欄位) 可獲得穩定最佳計畫。IN 適用於小型靜態清單；ANY/ALL 較少見，確認語意後再用。