Oracle 不等於運算子與 NULL 陷阱：<>、!=、~=、^= 差異與正確寫法

Oracle 提供四種不等於運算子：<>、!=、~=、^=，功能完全相同。但所有比較運算子遇到 NULL 結果皆為 UNKNOWN（視同 FALSE），導致 WHERE col <> 100 無法撈出 col IS NULL 的列。本文說明陷阱與三種正確解法。

四種不等於寫法等價

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 陷阱實測

CREATE TABLE test (name VARCHAR2(20));
INSERT INTO test VALUES ('xn');
INSERT INTO test VALUES ('abc');
INSERT INTO test VALUES (NULL);

SELECT * FROM test WHERE name <> 'xn';
-- 僅回傳 'abc'，NULL 列消失！

三種正確解法

1. 額外加上 IS NULL 判斷（最直觀）

SELECT * FROM test WHERE name <> 'xn' OR name IS NULL;

2. 使用 NVL 轉換 NULL 為非衝突值

SELECT * FROM test WHERE NVL(name, '<>') <> 'xn';
-- 將 NULL 替換為絕不會出現的標記

3. 使用 INSTR + CONCAT 繞過 NULL（舊版相容寫法）

SELECT * FROM test WHERE INSTR(CONCAT(name, 'xx'), 'xn') = 0;
-- CONCAT 遇 NULL 回傳另一參數，故 CONCAT(NULL,'xx')='xx'，INSTR('xx','xn')=0 符合條件

效能與可讀性比較

核心法則

NULL 只能用 IS NULL / IS NOT NULL 判斷，任何比較運算子（=、<>、>、<、LIKE、IN 等）與 NULL 運算結果皆為 UNKNOWN，WHERE/HAVING 視同 FALSE 過濾掉。

實務建議

• 設計階段：關鍵欄位設 NOT NULL，從源頭消除問題。
• 查詢階段：習慣寫 col <> val OR col IS NULL 或 NOT (col = val)（同樣需加 IS NULL）。
• 避免在 WHERE 條件對欄位使用函數（NVL, CONCAT, SUBSTR 等），導致全表掃描。