SQL QnA: NULL處理應注意事項及常用函數

1. NULL意義

現實生活中許多情況無法確知結果或資料值，如員工的離職日期，無法於就任時得知，需待離職生效後才能確定，資料庫對於此種未知（Unknown）、不明、未定義或類似意義的資料，用NULL定義表示。

l NULL值的比較：NULL代表未知值，因此不等於任何值，當然也包括另一個NULL值。

查詢中欲篩選NULL資料值時，需使用IS NULL或IS NOT NULL指令。

但彙總運算，在應用DISTINCT以選取非重覆值時，也會將所有NULL資料合併成一筆，前述兩種情況下則將NULL值視為相同。

l 排序： ORACLE視NULL為最大值，MSSQL預設為最小值。

ORACLE提供NULLS FIRST或NULLS LAST關鍵字，無視於原排序昇冪（ASC）或降冪（DESC）強制將NULL值置於最前或最後。MSSQL會將NULL值置於最前，但可用CASE運算式將NULL值置於最後。

l 計算限制：

ü 大部分的彙總函數（Aggregate Functions）是針對非NULL值的資料集進行總合運算，但COUNT(*)計數函數則傳回所有資料列筆數，NULL將被計入，可參《NULL對運算上之影響》。

ü 任何數值與NULL進行運算其結果均為NULL。後續可應用於除零錯誤處理。

ü 字串與NULL進行字串連結，MSSQL傳統作法將傳回NULL，如以CONCAT指令(2012版本)則ORACLE相同。

l 用預設值來代替NULL

NULL使用上/查詢上有諸多限制及應注意事項，當查詢NULL資料時往往造成索引（INDEX）失效等問題，可利用一些特定具有意義（如UNKOWN）的資料替代使用，則可避免前述限制與困擾。

2. NULL常用函數

NULL值的特性及處理上相當特殊，各種資料庫均為此提供一些專用函數。ORACLE及MSSQL兩者均提供具有相同功能及用法的COALESCE及NULLIF兩種ANSI SQL函數，此外，兩者分別提供與COALESCE功能近似的ISNULL及NVL函數，整理以下。

#	功能	MSSQL	ORACLE	說明
1	以其他資料置換NULL資料	ISNULL(expr1, expr2)	NVL(expr1, expr2)	若expr1為NULL則傳回expr2，否則expr1。等同COALESCE(expr1, expr2)
2	取得一連串運算式中，第一個非NULL資料值（置換）	COALESCE(expr1, expr2…, exprN)		ANSI-SQL建議使用
3	判斷運算式結果相同則傳回NULL	NULLIF(expr1,expr2)		運算式相等(expr1=expr2)傳回NULL值，否則傳回expr1 運算式不相等(expr1<>expr2)則expr1。

以下將測試MSSQL 、ORACLE資料庫特有NULL置換函數ISNULL及NVL，再採用ANSI標準指令COALESCE函數達成相同功能。

MSSQL	ORACLE
SELECT --測試1: 橺位A為NULL，以[新值]（非NULL值）替代 ISNULL(A, '新值') "test1.A" , COALESCE(A, '新值') "test1.B" --測試2: 橺位A、B均NULL，以欄位C（非NULL值）替代 , COALESCE(A, B, C) "test2.A" , ISNULL(A, ISNULL(B, C)) "test2.B" FROM ( SELECT NULL A , NULL B , '非NULL資料' C ) A	SELECT --測試1: 橺位A為NULL，以[新值]（非NULL值）替代 NVL(A, '新值') "test1.A" , COALESCE(A, '新值') "test1.B" --測試2: 橺位A、B均NULL，以欄位C（非NULL值）替代 , COALESCE(A, B, C) "test2.A" , NVL(A, NVL(B, C)) "test2.B" FROM ( SELECT NULL A , NULL B , '非NULL資料' C FROM DUAL )

兩者執行結果相同，如下：

test1.A	test1.B	test2.A	test2.B
新值	新值	非NULL資料	非NULL資料

測試1：欄位A為NULL，以[新值]（非NULL值）替代

由於欄位A值為NULL，因此將採用第二個引數值。ISNULL（MSSQL）、NVL（ORACLE）及COALESCE函數執行結果相同。

測試2：橺位A、B均NULL，以欄位C（非NULL值）替代

本測試具有3個參數，但ISNULL（MSSQL）及NVL（ORACLE）函數只支援二個參數，因此需採用巢狀呼叫模式，由於欄位A、B均為NULL，將採用第三個欄位。

可直接用COALESCE函數置換置代ISNULL及NVL函數，直接使用，但在多組判斷處理時更簡便，因此強烈建議採用ANSI-SQL指令COALESCE。

SQL QnA