Returns a null value if the two specified expressions are equal.
NULLIF ( expression , expression )
Is any valid scalar expression.
For more information, see Expressions (Transact-SQL)
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 function 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 evaluted 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
NULL), combine the
USE AdventureWorks; GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'budgets') DROP TABLE budgets; GO SET NOCOUNT ON; CREATE TABLE 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. Comparing NULLIF and CASE
To show the similarity between NULLIF and CASE, the following queries evaluate whether the values in the
FinishedGoodsFlag columns are the same. The first query uses
NULLIF. The second query uses the
USE AdventureWorks; 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
Help and Information
12 December 2006
14 April 2006