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 END

Incorrect 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'