SQL Server：認識 NULLIF 運算式

NULLIF 運算式
如果兩個指定的運算式相等，便傳回 Null 值。

NULLIF 運算式回傳值的規則，整理如下：

(1) 如果兩個運算式「不相等」，NULLIF 會傳回第一個 expression。
(2) 如果運算式「相等」，NULLIF 會傳回第一個 expression 之類型的 Null 值。

NULLIF 相當於兩個運算式相等且產生的運算式為 NULL 的搜尋 CASE 運算式。

語法：

NULLIF ( expression , expression )

NULL 值表示是未知的值。
NULL 值與空的值或零值不同。

兩個 Null 值永遠不會相等。

因為每個 NULL 的值都是未知的，兩個 Null 值之間、或是一個 NULL 與其他任何值之間的比較都會傳回未知的。

---

請參考以下的範例程式碼：

EX1：認識 NULLIF 運算式

-- NULLIF 運算式：資料庫定序：區分大小寫
SELECT NULLIF(1 ,2) , NULLIF(1,1), NULLIF('AB','Ab'), NULLIF('AB','AC')
GO

-- 01_NULLIF 運算式：資料庫定序：區分大小寫

-- NULLIF 運算式：資料庫定序：不區分大小寫
USE Northwind
GO
SELECT NULLIF(1 ,2) , NULLIF(1,1), NULLIF('AB','Ab'), NULLIF('AB','AC')
GO

-- 02_NULLIF 運算式：資料庫定序：不區分大小寫

-- NULLIF() 運算式：搭配變數
DECLARE @c1 INT =10 ,@c2 INT = NULL

SELECT NULLIF(@c1 , @c2) 
GO

-- 03_ NULLIF() 運算式：搭配變數

---

EX2：使用 NULLIF 運算式來處理分母為 0 的情形

-- 分母為 0，產生錯誤訊息：8134。
DECLARE @c1 INT=0

SELECT 100/@c1
GO
/*
錯誤訊息：
訊息 8134，層級 16，狀態 1，行 3
發現除以零的錯誤。
*/

-- 04_分母為 0，產生錯誤訊息：8134

-- 使用 NULLIF 運算式來處理分母為 0 的情形
DECLARE @c1 INT=0

SELECT 100/NULLIF(@c1,0) N'運算結果'
GO
/*
回傳：NULL
*/

-- 05_使用 NULLIF 運算式來處理分母為 0 的情形

---

EX3：比較 NULLIF 和 CASE

下列查詢會評估 UnitsInStock 和 ReorderLevel 資料行中的值是否相同，以顯示 NULLIF 和 CASE 之間的相似度。

第一個查詢使用 NULLIF。第二個查詢則使用 CASE 運算式。

NULLIF 相當於兩個運算式相等且產生的運算式為 NULL 的搜尋 CASE 運算式。

-- 使用 NULLIF 運算式
USE Northwind
GO
SELECT ProductID, ProductName, UnitsInStock, ReorderLevel, NULLIF(UnitsInStock, ReorderLevel) N'運算結果'
FROM Products
GO

-- 使用 CASE 運算式
SELECT ProductID, ProductName, UnitsInStock, ReorderLevel,
 CASE
  WHEN UnitsInStock = ReorderLevel THEN NULL
  ELSE UnitsInStock
 END N'運算結果'
FROM Products
GO

-- 06_比較 NULLIF 和 CASE

---

認識 NULLIF 運算式

如果兩個指定的運算式相等，便傳回 Null 值。
語法：

NULLIF ( expression , expression )

引數

expression
這是任何有效的純量運算式。

傳回類型

傳回與第一個 expression 相同的類型。

如果兩個運算式不相等，NULLIF 會傳回第一個 expression。
如果運算式相等，NULLIF 會傳回第一個 expression 之類型的 Null 值。

注意事項

NULLIF 相當於兩個運算式相等且產生的運算式為 NULL 的搜尋 CASE 運算式。

我們建議您不要在 NULLIF 函數中使用時間相依函數，例如 RAND()。
這可能會導致系統評估此函數兩次，並且傳回與兩個引動過程不同的結果。

---

參考資料

NULLIF (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms177562

運算式 (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms190286

T-SQL：認識 COALESCE() 函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce.html

T-SQL：認識 COALESCE() 函數，補充範例
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce_14.html