NULLIF() Function in SQL Server

Pre-requisites: NULL values

NULLIF() function in SQL Server is used to check if the two specified expressions are equal or not. The ISNULL() function substitutes a placed value for the Null value. The ISNULL () function is frequently used in a variety of circumstances, including switching the Null value in joins and selecting statements from one value to another.

If two arguments passed to a function are equal, the NULLIF () function returns Null to us. Like a case statement, the NULLIF() function operates. It will return the value of the first argument if the two arguments are not identical.

Features

1. This function is used to check if the two given expressions are equal or not.
2. This function returns NULL if the given two expressions are equal.
3. This function returns the first expression if the two given expressions are not equal.
4. This function comes under Advanced Functions.
5. This function accepts two parameters i.e, the first expression, and the second expression.

Syntax:

NULLIF(expr1, expr2)

Parameter Explanation: 

• NULLIF accepts two parameters, Where specified expressions are to be compared.
• Returns: It returns NULL if the given two expressions are equal else it returns the first expression if the two given expressions are not equal.

Let’s understand in a very simple way that if we have two expressions and we are using NULLIF() function then if both expressions are equal then it will return null else it will return the first expression.

Example-1 

Using the NULLIF() function and getting the output.

Here, we have two integers 11 and we are comparing if they are equal or not if both are equal then it will return null or else the first expression.

Query:

SELECT NULLIF(11, 11);

Output:

Here, NULL is returned as both expressions are equal.

Example-2 

Here we have basically two strings and we are checking if they are equal or not as it is equal it will return the first expression.

Query:

SELECT NULLIF('ab', 'abc');

Output:

Here, ‘ab’ is returned as output as the stated expressions are not equal.

Example-3

Using NULLIF() function and getting the output using variables.

Query:

DECLARE @exp1 VARCHAR(50);
DECLARE @exp2 VARCHAR(50);
SET @exp1 = '2021/01/08';
SET @exp2 = '2021/01/08';
SELECT NULLIF(@exp1, @exp2);

Output:

Example-4

Here we are using NULLIF() function and getting the output using CAST() function.

Query:

SELECT NULLIF(11, CAST(11.65 AS int));

Output:

Applications 

This function is used to test if the two stated expressions are equal or not.