NULLIF (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a null value if the two specified expressions are equal. 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. The second column returns the first value (5) because the two input values are different.

Topic link icon Transact-SQL Syntax Conventions


NULLIF ( expression , expression )  


Is any valid scalar expression.

Return Types

Returns the same type as the first expression.

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.


NULLIF is equivalent to a searched CASE expression in which the two expressions are equal and the resulting expression is NULL.

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.


A. Returning budget amounts that have not changed

The following example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year). For 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. 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);  
   previous_year), 0.00)) AS 'Average Budget'  
FROM budgets;  

Here is the result set.

Average Budget  
(1 row(s) affected)

B. Comparing NULLIF and CASE

To show the similarity between NULLIF and CASE, the following queries evaluate whether the values in the MakeFlag and FinishedGoodsFlag columns are the same. The first query uses NULLIF. The second query uses the CASE expression.

USE AdventureWorks2012;  
SELECT ProductID, MakeFlag, FinishedGoodsFlag,   
   NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'  
FROM Production.Product  
WHERE ProductID < 10;  

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =  
       WHEN MakeFlag = FinishedGoodsFlag THEN NULL  
       ELSE MakeFlag  
FROM Production.Product  
WHERE ProductID < 10;  

C: Returning budget amounts that contain no data

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)
decimal and numeric (Transact-SQL)
System Functions (Transact-SQL)