SQLite Tutorial SQLite Advanced SQLite Database SQLite References

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

expr1, expr2 Required. Specify two expressions to be compared.

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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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:

EmpIDNameCityAgeSalaryResult
1JohnLondon253000London
2MarryNew York242750New York
3JoParis272800NULL
4KimAmsterdam303100Amsterdam
5RameshNew Delhi283000New Delhi
6HuangBeijing282800Beijing

❮ SQLite Functions