MariaDB NULLIF() Function

In MariaDB, NULLIF() is a built-in function that returns NULL if the two given arguments are equal, otherwise returns the first argument.

MariaDB NULLIF() Syntax

Here is the syntax of the MariaDB NULLIF() function:

NULLIF(expr1, expr2)

The NULLIF() function can be implemented using CASE expression. NULLIF(expr1, expr2) is equivalent to CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Parameters

expr1

Optional. A value or expression.

expr2

Optional. Another value or expression.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'NULLIF'.

Return value

If expr1 equals to expr2, the NULLIF() function returns NULL, otherwise returns expr1.

MariaDB NULLIF() Examples

SELECT
    NULLIF(1, 1),
    NULLIF(1, 2);

+--------------+--------------+
| NULLIF(1, 1) | NULLIF(1, 2) |
+--------------+--------------+
|         NULL |            1 |
+--------------+--------------+

The following statements can be implemented using CASE expression:

SELECT
    CASE WHEN 1 = 1 THEN NULL ELSE 1 END,
    CASE WHEN 1 = 2 THEN NULL ELSE 1 END;

+--------------------------------------+--------------------------------------+
| CASE WHEN 1 = 1 THEN NULL ELSE 1 END | CASE WHEN 1 = 2 THEN NULL ELSE 1 END |
+--------------------------------------+--------------------------------------+
|                                 NULL |                                    1 |
+--------------------------------------+--------------------------------------+

Conclusion

In MariaDB, NULLIF() is a built-in function that returns if two given arguments are equal NULL, otherwise, it returns the first argument.