SQL Tutorial SQL Advanced SQL Database SQL References

MySQL NULLIF() Function



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

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.

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

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

mysql> SELECT NULLIF(123, 456);
Result: 123

mysql> SELECT NULLIF(123, 123);
Result: NULL

mysql> 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

❮ MySQL Functions