Oracle 中有四種「不等於」運算子:<>、!=、~=、^=,功能等價。但開發者常踩中一個經典陷阱:**任何與 NULL 的比較(含不等於)結果皆為 UNKNOWN(視同 FALSE),導致 NULL 值列被靜默過濾掉**。本文深度解析此行為、實測驗證、正確寫法、字串欄位同樣問題、INSTR/NVL 替代方案,以及 ANSI SQL 三值邏輯本質。
SELECT * FROM product WHERE price <> 180000;
SELECT * FROM product WHERE price != 180000;
SELECT * FROM product WHERE price ~= 180000;
SELECT * FROM product WHERE price ^= 180000;
-- 四者語意完全相同,執行計劃相同
-- 假設 product 表有 price NUMBER 欄位,部分列為 NULL
SELECT id, name FROM product WHERE price <> 180000;
-- 結果:price=180000 的列被過濾,**price IS NULL 的列也不會出現!**
原因: NULL <> 180000 → UNKNOWN → WHERE 視為 FALSE → 列被排除。
SELECT id, name FROM product
WHERE price <> 180000 OR price IS NULL;
SELECT * FROM test WHERE name <> 'xn';
-- name 為 NULL 的列不會出現
-- INSTR 回傳 0 表示未找到
-- CONCAT(name, 'xx') 確保 NULL 變為 'xx' 而非 NULL
SELECT * FROM test
WHERE INSTR(CONCAT(name, 'xx'), 'xn') = 0;
邏輯:name=NULL → CONCAT='xx' → INSTR('xx','xn')=0 → 條件成立 → 列被選取。
-- 將 NULL 轉為不會衝突的預設值
SELECT * FROM test WHERE NVL(name, 'xx') <> 'xn';
-- 或 COALESCE (ANSI 標準)
SELECT * FROM test WHERE COALESCE(name, 'xx') <> 'xn';
'xx' 應選擇業務上不可能出現的值。COALESCE 可支援多備選值,跨資料庫可攜性較好。SELECT * FROM test WHERE name <> 'xn' OR name IS NULL;
-- 或
SELECT * FROM test WHERE name IS NULL OR name != 'xn';
SQL 採用三值邏輯:TRUE、FALSE、UNKNOWN。
| 運算 | 結果 | WHERE 判定 |
|---|---|---|
| NULL = NULL | UNKNOWN | FALSE (不選取) |
| NULL <> NULL | UNKNOWN | FALSE |
| NULL = 'value' | UNKNOWN | FALSE |
| NULL <> 'value' | UNKNOWN | FALSE |
| NULL IS NULL | TRUE | TRUE (選取) |
| NULL IS NOT NULL | FALSE | FALSE |