In MySQL, NULLIF() is a flow control function that returns NULL if both of its arguments are equivalent. Otherwise it returns the first argument.
Syntax
The syntax goes like this:
NULLIF(expr1,expr2)Example
Here’s an example to demonstrate:
SELECT NULLIF( 7, 7 );Result:
NULL
In this case, both arguments are identical and so the result is NULL.
When the Arguments are Not Equivalent
Here’s what happens when the arguments are not equivalent:
SELECT NULLIF( 7, 8 );Result:
7
The arguments are different and so the first argument is returned.
Strings
Here’s an example that compares strings:
SELECT
NULLIF( 'Bean', 'Bean' ) AS "Same",
NULLIF( 'Bean', 'Mushroom' ) AS "Different";Result:
Same Different ---- --------- NULL Bean
Dates
Here’s an example that compares dates:
SELECT
NULLIF( DATE '2030-12-20', DATE '2030-12-20' ) AS "Same",
NULLIF( DATE '2030-12-20', DATE '2035-08-15' ) AS "Different";Result:
Same Different ---- ---------- NULL 2030-12-20
Expressions
NULLIF() evaluates the current value of the expressions. Therefore, if we pass an expression like this:
SELECT NULLIF( 8, 2 * 4 );We get this:
NULL
2 multiplied by 4 is 8, and so the two arguments are equivalent.
Here’s what happens if we change the second argument:
SELECT NULLIF( 8, 2 * 3 );Result:
8
The first argument is returned.
A Database Example
Suppose we run the following query:
SELECT
Name,
LocalName
FROM country
WHERE Region = 'Southern Europe'
ORDER BY Name;Result:
+-------------------------------+--------------------------------+ | Name | LocalName | +-------------------------------+--------------------------------+ | Albania | Shqipëria | | Andorra | Andorra | | Bosnia and Herzegovina | Bosna i Hercegovina | | Croatia | Hrvatska | | Gibraltar | Gibraltar | | Greece | Elláda | | Holy See (Vatican City State) | Santa Sede/Città del Vaticano | | Italy | Italia | | Macedonia | Makedonija | | Malta | Malta | | Portugal | Portugal | | San Marino | San Marino | | Slovenia | Slovenija | | Spain | España | | Yugoslavia | Jugoslavija | +-------------------------------+--------------------------------+
Here, we have country names in the left column, and the local name for the respective country in the right.
Let’s add NULLIF() to a third column of our query:
SELECT
Name,
LocalName,
NULLIF(LocalName, Name) AS "Local Name if Different"
FROM country
WHERE Region = 'Southern Europe'
ORDER BY Name;Result:
+-------------------------------+--------------------------------+--------------------------------+ | Name | LocalName | Local Name if Different | +-------------------------------+--------------------------------+--------------------------------+ | Albania | Shqipëria | Shqipëria | | Andorra | Andorra | NULL | | Bosnia and Herzegovina | Bosna i Hercegovina | Bosna i Hercegovina | | Croatia | Hrvatska | Hrvatska | | Gibraltar | Gibraltar | NULL | | Greece | Elláda | Elláda | | Holy See (Vatican City State) | Santa Sede/Città del Vaticano | Santa Sede/Città del Vaticano | | Italy | Italia | Italia | | Macedonia | Makedonija | Makedonija | | Malta | Malta | NULL | | Portugal | Portugal | NULL | | San Marino | San Marino | NULL | | Slovenia | Slovenija | Slovenija | | Spain | España | España | | Yugoslavia | Jugoslavija | Jugoslavija | +-------------------------------+--------------------------------+--------------------------------+
We can see that the third column returns the local name only if it’s different to the value in the Name column. If it’s the same, then NULL is returned.
We can also use NULLIF() to filter our query results:
SELECT
Name,
LocalName
FROM country
WHERE Region = 'Southern Europe'
AND NULLIF(LocalName, Name) IS NOT NULL
ORDER BY Name;Result:
+-------------------------------+--------------------------------+ | Name | LocalName | +-------------------------------+--------------------------------+ | Albania | Shqipëria | | Bosnia and Herzegovina | Bosna i Hercegovina | | Croatia | Hrvatska | | Greece | Elláda | | Holy See (Vatican City State) | Santa Sede/Città del Vaticano | | Italy | Italia | | Macedonia | Makedonija | | Slovenia | Slovenija | | Spain | España | | Yugoslavia | Jugoslavija | +-------------------------------+--------------------------------+
In this case, we returned only those rows where the local name is different to the Name column.
NULLIF() vs CASE
The following code:
NULLIF(expr1,expr2)is equivalent to the following CASE expression:
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 ENDIncorrect Parameter Count
Passing the wrong number of arguments, results in an error:
SELECT NULLIF( 5 );Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'NULLIF'