SQLite NULLIF() Function

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

The NULLIF() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If neither argument to NULLIF() defines a collating function then the BINARY collating function is used.

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

SELECT NULLIF(NULL, NULL);
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: