Oracle 預設排序將 NULL 視為最大值(ORDER BY col ASC → NULLS LAST,DESC → NULLS FIRST)。業務常需「NULL 排最前」或「NULL 排最後」的自訂邏輯。本文提供標準語法與相容舊版的替代方案。
-- NULL 排最前 (ASC) SELECT * FROM t ORDER BY col ASC NULLS FIRST; -- NULL 排最後 (ASC) SELECT * FROM t ORDER BY col ASC NULLS LAST; -- 組合:先按狀態排序,狀態相同再按金額 DESC,NULL 金額排最後 SELECT * FROM orders ORDER BY status, amount DESC NULLS LAST;
-- 數值欄位:NULL 視為 -1 (最小) 或 999999999 (最大) ORDER BY NVL(amount, -1) ASC; -- NULL 排最前 ORDER BY NVL(amount, 999999999) ASC; -- NULL 排最後 -- 字串欄位:NULL 視為 ' ' 或 '~' (ASCII 最大) ORDER BY NVL(name, ' ') ASC; -- NULL 排最前 ORDER BY NVL(name, '~') ASC; -- NULL 排最後
ORDER BY CASE WHEN col IS NULL THEN 0 ELSE 1 END, -- NULL 優先 (0<1) col; -- 非 NULL 內部正常排序 -- 或反轉 ORDER BY CASE WHEN col IS NULL THEN 1 ELSE 0 END, -- NULL 最後 col;
-- 建立函數索引支援 NVL 排序 CREATE INDEX idx_t_col_nvl ON t (NVL(col, ' ')); -- 查詢直接用 NVL,優化器可使用索引 SELECT * FROM t ORDER BY NVL(col, ' ') ASC;
-- 含 NULL 欄位的分頁,必須顯式指定 NULLS FIRST/LAST
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM large_table
ORDER BY created_date DESC NULLS LAST, id -- 確保確定性排序
) a WHERE ROWNUM <= :end_row
) WHERE rn > :start_row;
NULLS FIRST/LAST 不會阻礙索引使用(索引預設存儲 NULL,除非 WHERE col IS NOT NULL 建立部分索引)。NVL/CASE 包裹欄位會導致一般 B*Tree 索引失效,需建立函數索引或改用標準語法。V$SQL_WORKAREA_ACTIVE、V$SORT_USAGE 確認是否落盤,調大 PGA_AGGREGATE_TARGET。掌握 NULLS FIRST/LAST 標準語法為首選,舊版或極端邏輯才用 NVL/CASE 並配合函數索引,確保排序邏輯正確且效能最優。