搜尋本站文章

2012-07-11

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