IN 與 EXISTS 差異與效能深度解析

IN 三類型式與轉換

1. 常數列表：WHERE f1 IN ('a','b') ≡ WHERE f1='a' OR f1='b' 或 UNION ALL。
2. 獨立子查詢：WHERE f1 IN (SELECT f1 FROM t2 WHERE fx='x') — 子查詢不相關，優化器通常自動轉為 EXISTS，效能相同。
3. 相關子查詢：WHERE f1 IN (SELECT f1 FROM t2 WHERE t2.fx = t1.fx) — 子查詢引用外部欄位，效能通常不如 EXISTS。

習慣建議：除常數列表外，優先用 EXISTS。

選擇決策樹

實測數據 (SQL Server 環境，邏輯通用)

測試 1：以 sysobjects (小) 對 syscolumns (大)

-- EXISTS
SELECT * FROM sysobjects WHERE EXISTS (SELECT 1 FROM syscolumns WHERE id=syscolumns.id)
-- 掃描 1 次，邏輯讀 3

-- IN
SELECT * FROM sysobjects WHERE id IN (SELECT id FROM syscolumns)
-- 掃描 47 次，邏輯讀 97

EXISTS 完勝 (大表在子查詢時)。

測試 2：以 syscolumns (大) 對 sysobjects (小)

-- EXISTS
SELECT * FROM syscolumns WHERE EXISTS (SELECT 1 FROM sysobjects WHERE id=syscolumns.id)
-- 掃描 1 次，邏輯讀 10，預讀 15

-- IN
SELECT * FROM syscolumns WHERE id IN (SELECT id FROM sysobjects)
-- 掃描 1 次，邏輯讀 10，無預讀

小表在子查詢時兩者接近，EXISTS 多預讀開銷。

關鍵結論：索引是決定性因素

• 相關欄位有索引 → EXISTS 通常更穩定高效。
• 無索引 → 兩者皆退化為 Nested Loop + Table Scan，差異縮小。

進階案例：分組 Top-N

-- NOT EXISTS 找每組最大 sid (600 萬列)
SELECT * FROM test a
WHERE NOT EXISTS (SELECT 1 FROM test WHERE sort=a.sort AND sid > a.sid)
-- 效率較 IN (MAX) 高 3 倍以上

-- 找重複值
-- IN (獨立子查詢，優化器緩存結果集)
SELECT * FROM @t WHERE v IN (SELECT v FROM @t GROUP BY v HAVING COUNT(*)>1)

-- EXISTS (相關子查詢，逐列執行)
SELECT * FROM @t a WHERE EXISTS (SELECT 1 FROM @t WHERE id!=a.id AND v=a.v)

獨立子查詢 IN 會被優化為「結果集緩存 + 檢查」，反而比逐列執行的 EXISTS 快。結論：視子查詢是否相關而定，不可一概而論。

最佳實踐清單

1. 單鍵半連接 → IN 可讀性佳。
2. 多鍵/複雜條件半連接 → EXISTS。
3. 需回傳雙表欄位 → JOIN。
4. 關鍵欄位必須建索引，否則語法選擇意義有限。
5. 使用 EXPLAIN / Visual Explain 驗證實際計劃。