SQL NULLIF() Explained

Posted on by Ian

Most major RDBMSs support the NULLIF() operator, which returns NULL if both of its arguments are equivalent. If the arguments not equivalent, NULLIF() returns the first argument.

NULLIF() is a SQL-standard feature (it’s included in the ISO/IEC 9075 specification).

Syntax

The syntax goes like this:

NULLIF (V1, V2)

This is equivalent to the following CASE expression:

CASE WHEN V1=V2 THEN NULL ELSE V1 END

Example

Here’s an example to demonstrate:

SELECT NULLIF( 12, 12 );

Result:

NULL

In this case, both arguments are identical and so the result is NULL.

Depending on your RDBMS, the actual output for NULL values may be different. For example, when using psql (for PostgreSQL), the empty string is output by default whenever a NULL value is returned (although this can be changed). It’s the same with SQLite (and this can also be changed).

When the Arguments are Not Equivalent

Here’s what happens when the arguments are not equivalent:

SELECT NULLIF( 12, 13 );

Result:

12

The arguments are different and so the first argument is returned.

Strings

Here’s an example that compares strings:

SELECT 
    NULLIF( 'Gym', 'Gym' ) AS "Same",
    NULLIF( 'Gym', 'Bag' ) AS "Different";

Result:

+------+-----------+
| Same | Different |
+------+-----------+
| NULL | Gym       |
+------+-----------+

Dates

Here’s an example that compares dates:

SELECT 
    NULLIF( DATE '2045-11-25', DATE '2045-11-25' ) AS "Same",
    NULLIF( DATE '2045-11-25', DATE '1990-08-15' ) AS "Different";

Result:

+------+------------+
| Same | Different  |
+------+------------+
| NULL | 2045-11-25 |
+------+------------+

Expressions

NULLIF() evaluates the current value of the expressions. Therefore, if we pass an expression like this:

SELECT NULLIF( 24, 2 * 12 );

We get this:

NULL

2 multiplied by 12 is 24, and so the two arguments are equivalent.

Here’s what happens if we change the second argument:

SELECT NULLIF( 24, 3 * 12 );

Result:

24

The first argument is returned.

A Database Example

Suppose we run the following query:

SELECT 
    Name,
    LocalName
FROM country 
WHERE Region = 'South America'
ORDER BY Name;

Result:

+------------------+-------------------+
| Name             | LocalName         |
+------------------+-------------------+
| Argentina        | Argentina         |
| Bolivia          | Bolivia           |
| Brazil           | Brasil            |
| Chile            | Chile             |
| Colombia         | Colombia          |
| Ecuador          | Ecuador           |
| Falkland Islands | Falkland Islands  |
| French Guiana    | Guyane française  |
| Guyana           | Guyana            |
| Paraguay         | Paraguay          |
| Peru             | Perú/Piruw        |
| Suriname         | Suriname          |
| Uruguay          | Uruguay           |
| Venezuela        | Venezuela         |
+------------------+-------------------+

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 = 'South America'
ORDER BY Name;

Result:

+------------------+-------------------+-------------------------+
| Name             | LocalName         | Local Name if Different |
+------------------+-------------------+-------------------------+
| Argentina        | Argentina         | NULL                    |
| Bolivia          | Bolivia           | NULL                    |
| Brazil           | Brasil            | Brasil                  |
| Chile            | Chile             | NULL                    |
| Colombia         | Colombia          | NULL                    |
| Ecuador          | Ecuador           | NULL                    |
| Falkland Islands | Falkland Islands  | NULL                    |
| French Guiana    | Guyane française  | Guyane française        |
| Guyana           | Guyana            | NULL                    |
| Paraguay         | Paraguay          | NULL                    |
| Peru             | Perú/Piruw        | Perú/Piruw              |
| Suriname         | Suriname          | NULL                    |
| Uruguay          | Uruguay           | NULL                    |
| Venezuela        | Venezuela         | NULL                    |
+------------------+-------------------+-------------------------+

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 = 'South America'
AND NULLIF(LocalName, Name) IS NOT NULL
ORDER BY Name;

Result:

+---------------+-------------------+
| Name          | LocalName         |
+---------------+-------------------+
| Brazil        | Brasil            |
| French Guiana | Guyane française  |
| Peru          | Perú/Piruw        |
+---------------+-------------------+

In this case, we returned only those rows where the local name is different to the Name column.

NULLIF() vs CASE

As mentioned, the following code:

NULLIF (V1, V2)

is equivalent to the following CASE expression:

CASE WHEN V1=V2 THEN NULL ELSE V1 END

So it is possible to use a CASE expression instead of NULLIF() if so desired. The NULLIF() function is basically a syntactical shortcut for the CASE expression.

So for example, we could replace the previous example with the following:

SELECT 
    Name,
    LocalName
FROM country 
WHERE Region = 'South America'
AND (CASE WHEN LocalName = Name THEN NULL ELSE LocalName END) IS NOT NULL
ORDER BY Name;

Result:

+---------------+-------------------+
| Name          | LocalName         |
+---------------+-------------------+
| Brazil        | Brasil            |
| French Guiana | Guyane française  |
| Peru          | Perú/Piruw        |
+---------------+-------------------+

However, the NULLIF() function is much more concise.

Incorrect Parameter Count

Passing the wrong number of arguments, results in an error:

SELECT NULLIF( 'One' );

Result in MySQL:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'NULLIF'