MySQL 子查詢作為條件實戰：IN/NOT IN/EXISTS 進階用法

子查詢在 WHERE 子句中的三大模式與效能優化策略

子查詢作為條件是 SQL 複雜業務邏輯表達的核心手段。本文系統梳理 IN/NOT IN/EXISTS/NOT EXISTS 四種模式的語義差異、執行計畫特徵、空值陷阱、優化重寫技巧，並給出選型決策樹。

一、核心語法範例

模式語義空值處理執行策略適用場景
IN外部值在子查詢結果集中子查詢含 NULL → 整體為 UNKNOWN半連接、物化、IN-to-EXISTS 轉換結果集小、有索引、確定無 NULL
NOT IN外部值不在子查詢結果集中子查詢含 NULL → 永遠為 FALSE/UNKNOWN（致命陷阱）反連接、通常比 NOT EXISTS 慢極少推薦，改用 NOT EXISTS
EXISTS子查詢至少返回一行忽略 NULL，僅判斷行存在性相關子查詢、逐行驅動、可用索引大表驅動小表、存在性檢查
NOT EXISTS子查詢無任何行返回忽略 NULL，邏輯直觀反連接、Anti-Join 優化差集查詢、異常檢測、首選方案


三、NOT IN 空值陷阱實證
-- 1. NOT IN → NOT EXISTS（消除空值風險）
SELECT * FROM t1 WHERE a NOT IN (SELECT b FROM t2);
→ SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

-- 2. IN → EXISTS（大結果集場景）
SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE cond);
→ SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a AND cond);

-- 3. 關聯子查詢 → JOIN（可選列投影）
SELECT t1.*, (SELECT name FROM t2 WHERE t2.id = t1.t2_id) AS t2_name
FROM t1;
→ SELECT t1.*, t2.name AS t2_name FROM t1 LEFT JOIN t2 ON t1.t2_id = t2.id;

-- 4. 多層嵌套 → CTE/臨時表（可讀性+效能）
WITH filtered AS (SELECT id FROM t2 WHERE status=3)
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM filtered);

六、選型決策樹
-- 測試數據：t1 100萬行，t2 10萬行，有索引
-- 方案 A：NOT IN
EXPLAIN ANALYZE SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2 WHERE status=3);

-- 方案 B：NOT EXISTS
EXPLAIN ANALYZE SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id AND status=3);

-- 方案 C：LEFT JOIN ... IS NULL
EXPLAIN ANALYZE SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.id=t2.id AND t2.status=3 WHERE t2.id IS NULL;

-- 觀察：rows、loops、actual time、join type（ALL vs ref vs eq_ref）

八、總結
子查詢條件選型鐵律：拒絕 NOT IN（空值陷阱）、優先 NOT EXISTS（語義清晰+優化器友好）、謹慎 IN（大結果集物化開銷）、善用 CTE 分解複雜邏輯。配合 EXPLAIN ANALYZE 觀察實際執行計畫，結合索引設計、統計信息鮮度，實現查詢效能確定性優化。