NULL 在 MySQL 中代表「值未知」而非空字符串或零,無法使用 = != 進行比較。本文系統梳理 NULL 處理的三大操作符、函數與子查詢陷阱。
| 操作符 | 語法 | 結果 | 說明 |
|---|---|---|---|
| IS NULL | col IS NULL | TRUE/FALSE | 值為 NULL 返回 TRUE |
| IS NOT NULL | col IS NOT NULL | TRUE/FALSE | 值不為 NULL 返回 TRUE |
<=> (NULL-safe equal) | col1 <=> col2 | TRUE/FALSE | 兩邊均為 NULL 返回 TRUE,等同於 col1 = col2 OR (col1 IS NULL AND col2 IS NULL) |
| 函數 | 語法 | 行為 | 示例 |
|---|---|---|---|
| IFNULL | IFNULL(expr1, expr2) | expr1 非 NULL 返回 expr1,否則 expr2 | IFNULL(NULL, 'N/A') → 'N/A' |
| COALESCE | COALESCE(v1, v2, ...) | 返回第一個非 NULL 參數 | COALESCE(NULL, NULL, 'a', 'b') → 'a' |
| NULLIF | NULLIF(expr1, expr2) | expr1 = expr2 返回 NULL,否則 expr1 | NULLIF(5, 5) → NULL |
CREATE TABLE t2 (col1 INT, col2 INT);
CREATE TABLE t3 (col1 INT, col2 INT);
INSERT INTO t2 VALUES (1,2), (1,3);
INSERT INTO t3 VALUES (1,2), (1,NULL);
-- NOT IN 子查詢
SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3);
-- 結果:Empty set ❌
-- NOT EXISTS 子查詢
SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHERE t3.col2 = t2.col2);
-- 結果:返回 (1,3) ✅
MySQL 三值邏輯:TRUE、FALSE、UNKNOWN(NULL 參與比較結果為 UNKNOWN)
col2 NOT IN (2, NULL)
≡ col2 <> 2 AND col2 <> NULL
≡ TRUE AND UNKNOWN
≡ UNKNOWN → WHERE 視為 FALSE
NOT EXISTS 逐行評估,子查詢無結果返回 TRUE,不受 NULL 影響。
WHERE col NOT IN (SELECT col FROM t WHERE col IS NOT NULL)NOT NULL DEFAULT '' 或 DEFAULT 0COUNT(*):統計行數,包含 NULLCOUNT(col):統計非 NULL 值個數,忽略 NULLSUM/AVG/MIN/MAX:忽略 NULL 值計算GROUP BY:將所有 NULL 視為同一組NOT NULLIS NULL / IS NOT NULLNOT IN,改用 NOT EXISTSCOALESCE(col, '默認值') 避免前端報錯UNIQUE NOT NULL 組合)