MySQL 5.0 NOT IN 子查詢效能陷阱與正確寫法

問題現象

在 MySQL 5.0 版本中，使用 NOT IN 搭配靜態值列表時，大量資料下效能極差甚至產生錯誤結果：

-- 效能差、大數據下可能出錯
SELECT * FROM `table` WHERE id NOT IN (1,2,3,10000);

正確寫法：使用子查詢

MySQL 5.0 正式支援子查詢，將靜態列表改為子查詢可穩定發揮效能：

-- 正確、適用大數據量
SELECT * FROM `table` WHERE id NOT IN (SELECT id FROM subtable);

核心差異解析

進階替代方案（更推薦）

實務上建議改用 NOT EXISTS 或 LEFT JOIN ... IS NULL，避免 NOT IN 遇到 NULL 值時的邏輯陷阱：

-- 方案 A：NOT EXISTS（語意清晰、效能穩定）
SELECT * FROM `table` t
WHERE NOT EXISTS (SELECT 1 FROM subtable s WHERE s.id = t.id);

-- 方案 B：LEFT JOIN + IS NULL（傳統高效寫法）
SELECT t.* FROM `table` t
LEFT JOIN subtable s ON s.id = t.id
WHERE s.id IS NULL;

總結

• MySQL 5.0+ 請優先使用子查詢或 NOT EXISTS/LEFT JOIN。
• 避免在大表上直接使用靜態值列表的 NOT IN。
• 注意 NOT IN 子查詢回傳含 NULL 會導致整體結果為空，NOT EXISTS 無此問題。