NULLIF (Transact-SQL)
source link: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql?view=sql-server-ver15
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
NULLIF (Transact-SQL)
- 09/08/2017
- 2 minutes to read
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
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.
Transact-SQL Syntax Conventions
Syntax
NULLIF ( expression , expression )
Arguments
expression
Is any valid scalar expression.
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
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.
Remarks
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.
Examples
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);
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 MakeFlag
and FinishedGoodsFlag
columns are the same. The first query uses NULLIF
. 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: Returning budget amounts that contain no data
The following example creates a budgets
table, loads data, and uses NULLIF
to return a null if current_year
is null or contains the same data as previous_year
.
Copy
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)
Is this page helpful?
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK