MariaDB NVL() Function

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

MariaDB NVL() has been introduced as a synonym for the IFNULL() function be compatible with Oracle.

MariaDB `NVL()` Syntax

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

NVL(expr1, expr2)

Parameters

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

Return value

If expr1 is not NULL, the NVL() function returns expr1, otherwise it returns expr2.

MariaDB `NVL()` Examples

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

Basic example

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

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

Default values

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

First, we create a table named test_nvl for demonstration:

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

Let’s insert some rows:

INSERT INTO test_nvl 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, NVL(col, 'Nothing') col_with_default
FROM
    test_nvl;

+------+------------------+
| 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