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相同。

資料庫	SQL	結果
MSSQL	SELECT NULL + 'A'       , CONCAT(NULL, 'A') --2012	NULL A
ORACLE	SELECT NULL || 'A' FROM DUAL	A

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。