NULL 排序問題 - ORACLE / MSSQL / SQLite 行為整理

今天學習到新知識 - 資料表欄位進行排序時，遇到 NULL 會怎麼辦？茲針對我平時較會遇到的資料庫：ORACLE、MSSQL 及 SQLite 做個整理。

我弄了一個模擬資料表，用 CorpName, StartDate, EndDate 記錄工作履歷：

查詢條件為「仍在職者(離職日為 NULL)最優先，再來比離職日、到職日，較晚的先列」。

ORACLE 的規則是 - NULL 預設 ASC 時最後顯示、DESC 時最先顯示，但可使用 NULLS FIRST、NULLS LAST 改變行為(亦即 ASC 時預設為 NULLS LAST、DESC 時 NULLS FIRST)。參考

因此，ORDER BY EndDate DESC, StartDate DESC 時，DESC 為 NULLS FIRST 最先出現，預設行為即符合我們的要求。

若想改成 NULL 放最後，可寫成 ORDER BY EndDate DESC NULLS LAST, StartDate DESC：

MSSQL 在排序時，Null 值會當做最低的可能值來處理。

因此 ORDER BY EndDate ASC 時排最前面，ORDER BY EndDate DESC 時排最後面：

MSSQL 沒 NULLS LAST 語法可用，替代做法是加上一段 CASE WHEN，寫成 ORDER BY CASE WHEN EndDate IS NULL THEN 0 ELSE 1 END, EndDate DESC, StartDate DESC：

插花，今天請 ChatGPT 幫我產生範例時，學到一次 INSERT 多筆 VALUES 寫法：參考 (官方文件的範例說明跟 T-SQL 指令對不起來，說明寫五筆 dbo.Departments 但範例是三筆 Production.UnitMeasure，原來不只我寫文章會錯亂 XD，看 Code 就好)

INSERT INTO resume 
VALUES (N'公司A', '2001-01-01', '2002-12-31'),  
       (N'公司B', '2003-01-01', '2005-06-30'),  
       (N'公司C', '2005-07-01', '2010-09-30'),  
       (N'公司D', '2011-01-01', NULL),
	   (N'兼職', '2004-01-01', NULL);  

最後來看 SQLite，SQLite 的行為接近 MSSQL，NULL 視為最小值 (When it comes to sorting, SQLite considers NULL to be smaller than any other value. 參考)，也支援上述一次多個 VALUES 寫法。但 SQLite 3.30.0 支援 NULLS FIRST 及 NULLS LAST，故可用 ORDER BY EndDate DESC NULLS FIRST, StartDate DESC 解決：

Summary about null ordering in ORACLE, MSSQL and SQLite.