萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 不等於運算子與 NULL 值比較陷阱深度解析

前言

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;
-- 四者語意完全相同,執行計劃相同

二、NULL 比較陷阱實測

2.1 數值欄位

-- 假設 product 表有 price NUMBER 欄位,部分列為 NULL
SELECT id, name FROM product WHERE price <> 180000;
-- 結果:price=180000 的列被過濾,**price IS NULL 的列也不會出現!**

原因: NULL <> 180000 → UNKNOWN → WHERE 視為 FALSE → 列被排除。

2.2 正確寫法:顯式包含 NULL

SELECT id, name FROM product 
WHERE price <> 180000 OR price IS NULL;

2.3 字串欄位同樣問題

SELECT * FROM test WHERE name <> 'xn';
-- name 為 NULL 的列不會出現

三、替代寫法與函數應用

3.1 INSTR + CONCAT 技巧(舊版常用)

-- 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 → 條件成立 → 列被選取。

3.2 NVL/COALESCE 正規化比較(推薦)

-- 將 NULL 轉為不會衝突的預設值
SELECT * FROM test WHERE NVL(name, 'xx') <> 'xn';
-- 或 COALESCE (ANSI 標準)
SELECT * FROM test WHERE COALESCE(name, 'xx') <> 'xn';
  • 預設值 'xx' 應選擇業務上不可能出現的值。
  • COALESCE 可支援多備選值,跨資料庫可攜性較好。

3.3 明確 IS NULL/IS NOT NULL(最清晰)

SELECT * FROM test WHERE name <> 'xn' OR name IS NULL;
-- 或
SELECT * FROM test WHERE name IS NULL OR name != 'xn';

四、三值邏輯本質解析

SQL 採用三值邏輯:TRUE、FALSE、UNKNOWN。

鐵律: **NULL 只能用 IS NULL / IS NOT NULL 判斷,任何其他運算子與 NULL 運算結果皆為 UNKNOWN(視同 FALSE)。**

五、實務最佳實務

  1. 設計階段:業務上「無值」有含義時,考慮 NOT NULL + DEFAULT,避免 NULL 複雜度。
  2. 查詢階段:凡涉及可為 NULL 的欄位比較,**務必顯式處理 NULL**(IS NULL、NVL、COALESCE)。
  3. 索引階段:WHERE col <> val 通常無法使用索引(全表掃描);若需高效排除,考慮 CASE 表達式建立函數索引或分區。
  4. 程式碼審查:將「比較運算子 + 可 NULL 欄位」列為必檢項目。

六、結語

Oracle 四種不等於運算子功能等同,但**與 NULL 比較永遠回傳 UNKNOWN** 是 ANSI SQL 三值邏輯的核心特性,非 Bug。理解此本質、養成顯式處理 NULL 習慣、善用 NVL/COALESCE/IS NULL,能避免 90% 的「資料莫名消失」Bug。代碼審查時,將「WHERE col <> val 且 col 可為 NULL」列為紅燈項目,強制要求顯式 NULL 處理。

copyright © 萬盛學電腦網 all rights reserved
運算結果WHERE 判定
NULL = NULLUNKNOWNFALSE (不選取)
NULL <> NULLUNKNOWNFALSE
NULL = 'value'UNKNOWNFALSE
NULL <> 'value'UNKNOWNFALSE
NULL IS NULLTRUETRUE (選取)
NULL IS NOT NULLFALSEFALSE