SQL Server NULLIF() Function

The SQL Server (Transact-SQL) NULLIF() function compares two expressions and returns NULL if they are equal. Otherwise, it returns the first expression.

Syntax

NULLIF(expr1, expr2)

Parameters

Return Value

Returns NULL if expr1 and expr2 are equal. Otherwise, returns expr1.

Example 1:

The example below shows the usage of NULLIF() function.

SELECT NULLIF('John', 'John');
Result: NULL

SELECT NULLIF('John', 'John_2005');
Result: 'John'

SELECT NULLIF(123, 456);
Result: 123

SELECT NULLIF(123, 123);
Result: NULL

Example 2:

Consider a database table called Employee with the following records:

In the query below, the NULLIF() function is used to check the City column records against 'Paris'. It means if the employee city is Paris, it returns NULL. Otherwise, it returns the column value.

SELECT *, NULLIF(City, 'Paris') AS Result 
FROM Employee;

This will produce the result as shown below: