Oracle NULL 值排序完全指南：NVL、CASE、NULLS FIRST/LAST 實戰

Oracle 中 NULL 排序行為與控制技巧

Oracle 預設視 NULL 為「最大值」：ORDER BY col ASC 時 NULL 排最後，DESC 時 NULL 排最前。業務常需改變此行為（如將未填寫的優先級字段視為最低優先級）。本文總結三類主流解法並給出演示。

1. 預設行為回顧

-- ASC：NULL 最後
SELECT * FROM t ORDER BY priority ASC;
-- DESC：NULL 最前
SELECT * FROM t ORDER BY priority DESC;

2. 方案一：轉換函數（NVL / DECODE / COALESCE）

將 NULL 轉為具體值再排序，最直觀但需注意數據類型匹配。

-- 數值列：NULL 視為 0
ORDER BY NVL(priority, 0) ASC;
-- 字串列：NULL 視為 'ZZZ' 排最後
ORDER BY NVL(status, 'ZZZ') ASC;

3. 方案二：CASE 表達式（Oracle 9i+）

靈活定義排序權重，支援複雜業務邏輯。

-- 讓 priority=2 的行固定排第一
ORDER BY CASE WHEN priority = 2 THEN 1 ELSE 0 END DESC, priority ASC;

-- 讓 priority=2 的行固定排最後（配合 NULLS LAST）
ORDER BY CASE WHEN priority = 2 THEN NULL ELSE 0 END ASC NULLS LAST;

4. 方案三：NULLS FIRST / NULLS LAST 關鍵字（Oracle 10g+ 推薦）

專門針對 NULL 排序的標準語法，語義清晰、無需轉換數值。

-- NULL 排最前
ORDER BY priority ASC NULLS FIRST;
-- NULL 排最後
ORDER BY priority ASC NULLS LAST;
-- 降序且 NULL 最後
ORDER BY priority DESC NULLS LAST;

5. 綜合示例與輸出對比

建測試表 t(val NUMBER) 插入 1,2,3,4,NULL，分別應用上述三種寫法，截圖見原文。結論：

• 簡單場景首選 NULLS FIRST/LAST，語法標準、可讀性最佳。
• 需自定義非 NULL 值優先級時用 CASE。
• 舊版本或需轉換為特定業務值時用 NVL/DECODE。

6. 避坑指南

• NULLS FIRST/LAST 僅能用於 ORDER BY 子句，不能用於 GROUP BY 或分析函數的 WINDOWING 子句。
• 含索引列的 ORDER BY NVL(col, ...) 可能導致索引失效，考慮建立函數索引。