NULLIF (Transact-SQL)NULLIF (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

如果兩個指定的運算式相等,便傳回 Null 值。Returns a null value if the two specified expressions are equal. 例如,SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different; 會針對第一個資料行 (4 和 4) 傳回 NULL,因為這兩個輸入值一樣。For example, SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different; returns NULL for the first column (4 and 4) because the two input values are the same. 第二個資料行會傳回第一個值 (5),因為這兩個輸入值不同。The second column returns the first value (5) because the two input values are different.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

NULLIF ( expression , expression )  

引數Arguments

expressionexpression
這是任何有效的純量運算式Is any valid scalar expression.

傳回類型Return Types

傳回與第一個 expression 相同的類型。Returns the same type as the first expression.

如果這兩個運算式不相等,NULLIF 會傳回第一個 expressionNULLIF returns the first expression if the two expressions are not equal. 如果運算式相等,NULLIF 會傳回第一個 expression 類型的 Null 值。If the expressions are equal, NULLIF returns a null value of the type of the first expression.

RemarksRemarks

NULLIF 相當於兩個運算式相等且產生的運算式為 NULL 的搜尋 CASE 運算式。NULLIF is equivalent to a searched CASE expression in which the two expressions are equal and the resulting expression is NULL.

我們建議您不要在 NULLIF 函數中使用時間相依函數,例如 RAND()。We recommend that you not use time-dependent functions, such as RAND(), within a NULLIF function. 這可能會導致系統評估此函數兩次,並從這兩個引動過程傳回不同的結果。This could cause the function to be evaluated twice and to return different results from the two invocations.

範例Examples

A.A. 傳回尚未變更的預算數量Returning budget amounts that have not changed

下列範例會建立一份 budgets 資料表,來顯示部門 (dept) 及其目前預算 (current_year) 和前一年的預算 (previous_year)。The following example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year). 對今年而言,如果部門預算與前一年相同,就使用 NULL,如果預算仍未確定,就使用 0For the current year, NULL is used for departments with budgets that have not changed from the previous year, and 0 is used for budgets that have not yet been determined. 若只要知道收到預算之部門的平均值,且要併入前一年的預算值 (使用 previous_year 值,其中 current_yearNULL),便將 NULLIFCOALESCE 函數組合起來。To find out the average of only those departments that receive a budget and to include the budget value from the previous year (use the previous_year value, where the current_year is NULL), combine the NULLIF and COALESCE functions.

CREATE TABLE dbo.budgets  
(  
   dept            tinyint   IDENTITY,  
   current_year      decimal   NULL,  
   previous_year   decimal   NULL  
);  
INSERT budgets VALUES(100000, 150000);  
INSERT budgets VALUES(NULL, 300000);  
INSERT budgets VALUES(0, 100000);  
INSERT budgets VALUES(NULL, 150000);  
INSERT budgets VALUES(300000, 250000);  
GO    
SET NOCOUNT OFF;  
SELECT AVG(NULLIF(COALESCE(current_year,  
   previous_year), 0.00)) AS 'Average Budget'  
FROM budgets;  
GO  

以下為結果集:Here is the result set.

Average Budget  
--------------  
212500.000000  
(1 row(s) affected)

B.B. 比較 NULLIF 和 CASEComparing NULLIF and CASE

下列查詢會評估 NULLIFCASE 資料行中的值是否相同,以顯示 MakeFlagFinishedGoodsFlag 之間的相似度。To show the similarity between NULLIF and CASE, the following queries evaluate whether the values in the MakeFlag and FinishedGoodsFlag columns are the same. 第一個查詢使用 NULLIFThe first query uses NULLIF. 第二個查詢則使用 CASE 運算式。The second query uses the CASE expression.

USE AdventureWorks2012;  
GO  
SELECT ProductID, MakeFlag, FinishedGoodsFlag,   
   NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'  
FROM Production.Product  
WHERE ProductID < 10;  
GO  
  
SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =  
   CASE  
       WHEN MakeFlag = FinishedGoodsFlag THEN NULL  
       ELSE MakeFlag  
   END  
FROM Production.Product  
WHERE ProductID < 10;  
GO  

C:傳回未包含資料的預算金額C: Returning budget amounts that contain no data

下列範例會建立 budgets 資料表、載入資料,然後使用 NULLIF 傳回 Null (如果 current_yearprevious_year 都未包含資料)。The following example creates a budgets table, loads data, and uses NULLIF to return a null if neither current_year nor previous_year contains data.

CREATE TABLE budgets (  
   dept           tinyint,  
   current_year   decimal(10,2),  
   previous_year  decimal(10,2)  
);  
  
INSERT INTO budgets VALUES(1, 100000, 150000);  
INSERT INTO budgets VALUES(2, NULL, 300000);  
INSERT INTO budgets VALUES(3, 0, 100000);  
INSERT INTO budgets VALUES(4, NULL, 150000);  
INSERT INTO budgets VALUES(5, 300000, 300000);  
  
SELECT dept, NULLIF(current_year,  
   previous_year) AS LastBudget  
FROM budgets;  

以下為結果集:Here is the result set.

dept   LastBudget  
----   -----------  
1      100000.00  
2      null 
3      0.00  
4      null  
5      null

另請參閱See Also

CASE (Transact-SQL) CASE (Transact-SQL)
decimal 和 numeric (Transact-SQL) decimal and numeric (Transact-SQL)
系統函數 (Transact-SQL)System Functions (Transact-SQL)