NULLIF() Function in SQL Server

Last Updated : 25 Jun, 2024

NULLIF() function in SQL Server is used to check if the two specified expressions are equal or not. If two arguments passed to a function are equal, the NULLIF() function returns Null to us. NULLIF() function operates like a Like a CASE Statement. It will return the value of the first argument if the two arguments are not identical.

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.

Syntax

NULLIF(expression1, expression2)

Parameters:

NULLIF() Function accepts two parameters,

expression1: This is the first expression to be compared.
expression2: This is the second expression to be compared with the first.

Return Value:

If expression1 equals expression2, the function returns NULL.
If expression1 does not equal expression2, the function returns expression1.

NULLIF() Function Examples

Let us take a look at some of the examples of NULLIF() Function in SQL Server.

Example 1: Comparing Two Integers Using the NULLIF() function

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:

NULLIF(11,11)
null

Here, NULL is returned as both expressions are equal.

Example 2: Comparing Two Strings Using the NULLIF() function

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:

NULLIF(‘ab’, ‘abc’)
ab

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

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

In this example, we use the NULLIF() function with 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:

null

Example 4: Using NULLIF() Function with CAST() Function

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

Query:

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

Output:

NULLIF(11, CAST(11.65 AS int))
null

Important Points About SQL Server NULLIF() Function

The NULLIF() function compares two expressions and returns NULL if they are equal; otherwise, it returns the first expression.

It is different from the ISNULL operator, which checks if a value is NULL, and from the ISNULL() function, which replaces NULL values with a specified value.

The NULLIF() function simplifies SQL queries by reducing the need for complex conditional logic using CASE statements.

It is commonly used to prevent errors such as division by zero or to handle specific conditional logic within queries.