In MariaDB, the NULLIF() function 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( 5, 5 );

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( 5, 3 );

Result:

5

In this case, the first argument is returned.

Strings

Here’s an example that compares strings:

SELECT 
    NULLIF( 'Papaya', 'Papaya' ) AS "Same",
    NULLIF( 'Papaya', 'Salad' ) AS "Different";

Result:

+------+-----------+
| Same | Different |
+------+-----------+
| NULL | Papaya    |
+------+-----------+

Dates

Here’s an example that compares dates:

SELECT 
    NULLIF( DATE '2000-10-30', DATE '2000-10-30' ) AS "Same",
    NULLIF( DATE '2000-10-30', DATE '1999-10-30' ) AS "Different";

Result:

+------+------------+
| Same | Different  |
+------+------------+
| NULL | 2000-10-30 |
+------+------------+

Expressions

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

SELECT NULLIF( 10, 2 * 5 );

We get this:

NULL

2 multiplied by 5 is 10, and so the two arguments are equivalent.

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

SELECT NULLIF( 10, 3 * 5 );

Result:

10

The first argument is returned.

A Database Example

Suppose we run the following query:

SELECT 
    Name,
    LocalName
FROM country 
ORDER BY Name ASC
LIMIT 10;

Result:

+---------------------+-----------------------+
| Name                | LocalName             |
+---------------------+-----------------------+
| Afghanistan         | Afganistan/Afqanestan |
| Albania             | Shqipëria             |
| Algeria             | Al-Jaza’ir/Algérie    |
| American Samoa      | Amerika Samoa         |
| Andorra             | Andorra               |
| Angola              | Angola                |
| Anguilla            | Anguilla              |
| Antarctica          | –                     |
| Antigua and Barbuda | Antigua and Barbuda   |
| Argentina           | Argentina             |
+---------------------+-----------------------+

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 Different"
FROM country 
ORDER BY Name ASC
LIMIT 10;

Result:

+---------------------+-----------------------+-----------------------+
| Name                | LocalName             | Local Name Different  |
+---------------------+-----------------------+-----------------------+
| Afghanistan         | Afganistan/Afqanestan | Afganistan/Afqanestan |
| Albania             | Shqipëria             | Shqipëria             |
| Algeria             | Al-Jaza’ir/Algérie    | Al-Jaza’ir/Algérie    |
| American Samoa      | Amerika Samoa         | Amerika Samoa         |
| Andorra             | Andorra               | NULL                  |
| Angola              | Angola                | NULL                  |
| Anguilla            | Anguilla              | NULL                  |
| Antarctica          | –                     | –                     |
| Antigua and Barbuda | Antigua and Barbuda   | NULL                  |
| Argentina           | Argentina             | 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 alternatively use NULLIF() to filter our query results:

SELECT 
    Name,
    LocalName
FROM country 
WHERE NULLIF(LocalName, Name) IS NOT NULL
ORDER BY Name ASC
LIMIT 10;

Result:

+----------------+-----------------------+
| Name           | LocalName             |
+----------------+-----------------------+
| Afghanistan    | Afganistan/Afqanestan |
| Albania        | Shqipëria             |
| Algeria        | Al-Jaza’ir/Algérie    |
| American Samoa | Amerika Samoa         |
| Antarctica     | –                     |
| Armenia        | Hajastan              |
| Austria        | Österreich            |
| Azerbaijan     | Azärbaycan            |
| Bahamas        | The Bahamas           |
| Bahrain        | Al-Bahrayn            |
+----------------+-----------------------+

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( 10 );

Result:

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