MySQL 十大反直覺陷阱：NULL 比較、LEFT JOIN 條件、NOT IN 子查詢與隨機排序

前言：那些「看起來對卻不對」的 SQL

MySQL 某些行為與標準 SQL 或直覺不符，稍有不慎即導致資料錯漏或效能災難。本文整理自經典譯文《10 things in MySQL (that won't work as expected)》，逐一拆解十大經典坑點，給出正確寫法與原理解析。

#10 搜尋 NULL 值：= NULL 永遠不匹配

-- 錯誤
SELECT * FROM a WHERE a.column = NULL;

-- 正確：使用 IS NULL
SELECT * FROM a WHERE a.column IS NULL;

原理：SQL 標準規定 NULL = NULL 為 UNKNOWN（非 TRUE），優化器會直接剔除此類條件。

#9 LEFT JOIN 後接 WHERE 條件會退化為 INNER JOIN

-- 錯誤：WHERE 在 JOIN 後過濾，導致無匹配行被剔除
SELECT * FROM a LEFT JOIN b ON b.a = a.id WHERE b.column = 'something';

-- 正確：將條件移至 ON 子句
SELECT * FROM a LEFT JOIN b ON b.a = a.id AND b.column = 'something';

原理：WHERE 在連接後執行，b.column = 'something' 要求非 NULL，故無匹配行的 a 記錄被過濾，實質變成 INNER JOIN。

#8 小於某值且非 NULL：IS NOT NULL 是冗餘的

-- 冗餘
SELECT * FROM b WHERE b.column < 'something' AND b.column IS NOT NULL;

-- 簡化
SELECT * FROM b WHERE b.column < 'something';

原理：任何與 NULL 比較（<、>、=）結果均為 UNKNOWN，不會通過 WHERE 過濾。但注意：ORDER BY 時 NULL 排最前，易誤以為 NULL 最小。

#7 以 NULL 為條件 JOIN：需額外 OR 條件

-- 錯誤：NULL 不等於 NULL，雙 NULL 行不會匹配
SELECT * FROM a JOIN b ON a.column = b.column;

-- 正確
SELECT * FROM a JOIN b ON a.column = b.column OR (a.column IS NULL AND b.column IS NULL);

MySQL 優化器識別此模式為 ref_or_null 存取策略，效率極高。

#6 NOT IN 子查詢遇 NULL 會回傳空結果

-- 危險：若 b.column 含 NULL，整個 NOT IN 結果為空
SELECT a.* FROM a WHERE a.column NOT IN (SELECT column FROM b);

-- 安全：改用 NOT EXISTS
SELECT a.* FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.column = a.column);

原理：NOT IN 內部邏輯等價於 NOT (col = val1 OR col = val2 OR ...)，若列表含 NULL，整式為 UNKNOWN。EXISTS 永遠回傳 TRUE/FALSE，不受 NULL 干擾。

#5 隨機樣本再排序：RAND() 破壞次序邏輯

-- 錯誤：RAND() 值幾不可能相等，第二排序鍵無意義
SELECT * FROM a ORDER BY RAND(), column LIMIT 10;

-- 正確：子查詢先取隨機樣本，外層再排序
SELECT * FROM (
  SELECT * FROM mytable ORDER BY RAND() LIMIT 10
) q ORDER BY column;

#4 依群組取任意記錄：DISTINCT 非函數、GROUP BY 非標準

-- 錯誤：DISTINCT 作用於所有欄位，括號無意義
SELECT DISTINCT(grouper), a.* FROM a;

-- 風險：MySQL 允許 GROUP BY 非聚合欄位，但返回值不確定
SELECT a.* FROM a GROUP BY grouper;

-- 安全寫法（需主鍵 id）
SELECT a.* FROM (
  SELECT DISTINCT grouper FROM a
) ao JOIN a ON a.id = (
  SELECT id FROM a ai WHERE ai.grouper = ao.grouper LIMIT 1
);

#3 依群組取最小 id 記錄：同樣需子查詢保證確定性

SELECT a.* FROM (
  SELECT DISTINCT grouper FROM a
) ao JOIN a ON a.id = (
  SELECT id FROM a ai WHERE ai.grouper = ao.grouper ORDER BY ai.id LIMIT 1
);

#2 IN 列表傳字串 '1,2,3' 不會自動展開

-- 錯誤：比較的是整個字串 '1,2,3'
SELECT * FROM a WHERE column IN ('1,2,3');

-- 正確：展開為獨立值
SELECT * FROM a WHERE column IN (1, 2, 3);

-- 無法改參數時：使用 FIND_IN_SET（全表掃描，無索引）
SELECT * FROM a WHERE FIND_IN_SET(column, '1,2,3');

#1 LEFT JOIN + COUNT(*) 永遠不為 0

-- 錯誤：無匹配行時 COUNT(*) 仍為 1（計算的是 a 的行）
SELECT a.id, COUNT(*) FROM a LEFT JOIN b ON b.a = a.id GROUP BY a.id;

-- 正確：計算 b 的非 NULL 欄位
SELECT a.id, COUNT(b.a) FROM a LEFT JOIN b ON b.a = a.id GROUP BY a.id;

原理：COUNT(*) 計算行數，COUNT(expr) 計算非 NULL 值。連接鍵 b.a 在無匹配時為 NULL，故計數為 0。

總結：養成「防衛性 SQL」習慣

• NULL 比較永遠用 IS NULL / IS NOT NULL
• LEFT JOIN 過濾條件寫在 ON 而非 WHERE
• 子查詢否定邏輯優先用 NOT EXISTS
• 隨機抽樣 + 排序必須分層
• 群組取代表記錄用子查詢 + LIMIT 1 保證確定性
• IN 列表勿傳字串，動態值用臨時表或 FIND_IN_SET（小表）
• 外連接計數用 COUNT(關聯欄位) 而非 COUNT(*)

深入理解這些細節，能讓你寫出更健壯、高效、符合預期的 MySQL 查詢。