MariaDB IFNULL() Function

In MariaDB, IFNULL() is a built-in function that returns the second parameter if the first parameter is NULL, otherwise returns the first parameter.

From MariaDB 10.3, NVL() is a synonym for IFNULL().

MariaDB `IFNULL()` Syntax

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

IFNULL(expr1, expr2)

Parameters

expr1: Optional. Determines whether this expression is NULL.
expr2: Optional. Returns expr2 when expr1 is NULL.

Return value

If expr1 yes NULL, the IFNULL() function returns expr1, otherwise it returns expr2.

MariaDB `IFNULL()` Examples

The following example shows the usage of the MariaDB IFNULL() function.

Basic example

SELECT IFNULL(NULL, 'It is NULL'),
    IFNULL('I am not NULL', 'I am NULL');

+----------------------------+--------------------------------------+
| IFNULL(NULL, 'It is NULL') | IFNULL('I am not NULL', 'I am NULL') |
+----------------------------+--------------------------------------+
| It is NULL                 | I am not NULL                        |
+----------------------------+--------------------------------------+

Generate default

The IFNULL() Function can be used to generate a default value for a column when query.

First, we create a table named test_ifnull for demonstration:

DROP TABLE IF EXISTS test_ifnull;
CREATE TABLE test_ifnull (
    col VARCHAR(50)
);

Let’s insert some rows for testing:

INSERT INTO test_ifnull VALUES ('A'), ('B'), (NULL), ('D');

Now the table has the following rows:

+------+
| col  |
+------+
| A    |
| B    |
| NULL |
| D    |
+------+

We see that there is a NULL value in the rows. If we want to set a default value Nothing for NULL values, use the following statement:

SELECT
    col, IFNULL(col, 'Nothing') col_with_default
FROM
    test_ifnull;

+------+------------------+
| col  | col_with_default |
+------+------------------+
| A    | A                |
| B    | B                |
| NULL | Nothing          |
| D    | D                |
+------+------------------+

We seethat the content of the col_with_default column in the row containing NULL becomes Nothing.

Conclusion