SQL NULLIF

SQL NULLIF expression compares two expression and returns a null value if the two specified expressions are equal, else it returns the first expression.

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

For example:

The NULLIF(@val1, @val2) expression is equivalent to below case expression:

CASE
WHEN @val1=@val2 THEN NULL
ELSE @val1 END

SYNTAX

NULLIF ( expression1 , expression2)

expression1, expression2

is the scalar expressions, that will be compared and the values of the expressions must be of the same datatype.

Returns the same type as the first expression1.

Lets take some example of using NULLIF in SQL Server.

Using NULLIF with integer values

Following statement returns the first value because first and second argument values are not equal.

SELECT NULLIF(15, 1) AS Result

[Also Read: SQL IS NULL]

Following statement returns null because the value of first argument is equal to the value of second argument.

SELECT NULLIF(15, 15) AS Result

Using NULLIF with string values

Following statement returns the first value because first and second argument values are not equal.

SELECT NULLIF('SQL', 'SQL Server') AS Result

Following statement returns null because the value of first argument is equal to the value of second argument.

NULLIF expression is equivalent to Searched CASE Statement

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

The NULLIF(@val1, @val2) expression is equivalent to below case expression:

CASE
WHEN @val1=@val2 THEN NULL
ELSE @val1 END

Lets take a look at following example.

DECLARE @Val1 INT = 15, @Val2 INT = 1;

SELECT
NULLIF(@Val1,@Val2) AS Result_1;

SELECT CASE
WHEN @val1=@val2 THEN NULL
ELSE @val1 END AS Result_2 ;

GO

You can see, the NULLIFF expression returns the first value that is 15 because first and second argument values are not equal. Also case expression returns 15.

 

Also Read:

SQL IS NULL

SQL IS NOT NULL

SQL Server NOT NULL Constraint

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 1,415 total views,  1 views today