如何避免除0錯誤-NULLIF函數之應用

在報表常需計算相關比率，對於除法運算中分母是否為零之檢查與處理影響甚鉅，若無適當處理將發生除零錯誤，在此將介紹在SQL中如何避免除零錯誤，除可應用CASE WHEN檢查分母(denominator)是否為零，若為零則傳回NULL，反之則傳回計算結果；另外，也可利用NULLIF函數即可處理。SQL SERVER及ORACLE均有支援此指令，SQL如下所示。

SQL SERVER	ORACLE
SELECT 100.0* 1/ NULLIF(Val, 0) "Ratio(%)" , '1/'+ CAST(VAL AS VARCHAR) + ' (' + TestCase +')' TestCase FROM ( SELECT 0 Val, '分母為零' TestCase UNION ALL SELECT 2 Val, '分母不為零' TestCase ) A	SELECT  1/ NULLIF(Val, 0) *100 "Ratio(%)" , '1/' || Val ||' (' || TestCase || ')' TestCase FROM ( SELECT 0 Val, '分母為零' TestCase FROM DUAL UNION ALL SELECT 2 Val, '分母不為零' TestCase FROM DUAL )

前述執行結果如下所示，利用任何值除以NULL運結果為NULL的特性，透過NULLIF函數將分母為零的資料轉換為NULL值，即可避免除零錯誤。

Ratio(%)	TestCase
	1/0 (分母為零)
50	1/2 (分母不為零)

 *註: 任何資料與NULL進行運算，只有少數狀況不為NULL。

以下將介紹NULLIF的使用。NULLIF語法如下:

NULLIF ( expr1 , expr2 )

若兩個運算式不相等，即expr1<>expr2，則NULLIF傳回expr1。若運算式相等(即expr1=expr2)，將傳回NULL值。前述利用CASE WHEN是判斷分母不為零時進行運算。另外，可將NULLIF函數轉換為CASE WHEN如下所示，可完全了解此函數的特性。但此種寫法與一般常用除零判斷不同，鮮少使用。

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

若對於NULLIF函數難以理解，建議可將NULLIF函數拆解成NULL IF，即如果成立（等於0）即傳回NULL。