Some RDBMSs provide an IFNULL() function that can be used when dealing with potentially null values. In particular, MySQL, MariaDB, and SQLite each have an IFNULL() function that replaces any null values with another value.

Other RDBMSs, such as SQL Server, Oracle, and PostgreSQL provide similar functionality via functions of a different name.

Example of IFNULL()

As mentioned, RDBMSs such as MySQL, MariaDB, and SQLite include an IFNULL() function for replacing null values with another value. Here’s an example of how IFNULL() works in those RDBMSs:

SELECT
    IFNULL( null, 'Dog' ),
    IFNULL( 'Horse', 'Dog' ),
    IFNULL( 'Horse', null );

Result:

+-----------------------+--------------------------+-------------------------+
| IFNULL( null, 'Dog' ) | IFNULL( 'Horse', 'Dog' ) | IFNULL( 'Horse', null ) |
+-----------------------+--------------------------+-------------------------+
| Dog                   | Horse                    | Horse                   |
+-----------------------+--------------------------+-------------------------+

How it works is, the IFNULL() function accepts two arguments. If the first one is null, then the second argument is returned. If the first argument is not null, then the first argument is returned.

IFNULL() evaluates the current value of any expression:

SELECT
    IFNULL( 3 / 0, 'Dog' ),
    IFNULL( 3 * 5, 'Dog' ),
    IFNULL( 'Horse', 1 / 0 );

Result:

+------------------------+------------------------+--------------------------+
| IFNULL( 3 / 0, 'Dog' ) | IFNULL( 3 * 5, 'Dog' ) | IFNULL( 'Horse', 1 / 0 ) |
+------------------------+------------------------+--------------------------+
| Dog                    | 15                     | Horse                    |
+------------------------+------------------------+--------------------------+
1 row in set, 1 warning (0.01 sec)

In the first column I tried to divide by zero, which results in a null value in MySQL (and a warning). Therefore the second argument is returned.

In the second column, the expression returns a non-null value, and so it is returned.

In the third column, the first argument is a non-null value and so it is returned. The second argument is not even evaluated in this case. To demonstrate this, here it is on its own:

SELECT IFNULL( 'Horse', 1 / 0 );

Result:

+--------------------------+
| IFNULL( 'Horse', 1 / 0 ) |
+--------------------------+
| Horse                    |
+--------------------------+
1 row in set (0.00 sec)

In this case MySQL doesn’t return a warning like before. That’s because the first argument is non-null.

Here it is again with the null constant as the first argument:

SELECT IFNULL( null, 1 / 0 );

Result:

+-----------------------+
| IFNULL( null, 1 / 0 ) |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

This time we get the warning. That’s because the first argument is null, and so it progressed to the second argument (which also resolves to null).

SQL Server – ISNULL()

SQL Server doesn’t have an IFNULL() function, but it does have the ISNULL() function that does the same thing that IFNULL() does in the RDBMSs mentioned above.

Example:

SELECT ISNULL( null, 'Horse' );

Result:

Horse

And here it is when the first argument is a non-null value:

SELECT ISNULL( 2 * 3, 'Horse' );

Result:

6

But if we try to divide by zero we get an error:

SELECT ISNULL( 2 / 0, 'Horse' );

Result:

Error: Divide by zero error encountered.

If you’ve used other RDBMSs, you might be confused about SQL Server’s implementation of ISNULL(). Other RDBMSs (such as MySQL, MariaDB, and Oracle) also have an ISNULL() function, but for a different purpose. In those RDBMSs, ISNULL() accepts just one argument. If the argument is null, ISNULL() returns 1, otherwise it returns 0.

Oracle Database – NVL()

Oracle Database has the NVL() function that does the same thing that IFNULL() does in other RDBMSs.

SELECT NVL(null, 'Fish')
FROM DUAL;

Result:

Fish

Oracle Database also has an NVL2() function that provides extra functionality to NVL(). Oracle’s NVL2() function accepts three arguments. This allows us to provide a different value for non-null values.

When the first argument is not null, the second argument is returned. Otherwise the third argument is returned.

SELECT NVL2(null, 2, 3)
FROM DUAL;

Result:

3

Here, the first argument was null and so the third argument was returned.

Here’s what happens when the first argument is not null:

SELECT NVL2(1, 2, 3)
FROM DUAL;

Result:

2

PostgreSQL – COALESCE()

PostgreSQL doesn’t have an IFNULL() function or an ISNULL() function. But it does support the SQL standard COALESCE() (as do all of the RDBMSs mentioned above).

COALESCE() returns the first non-null argument. Therefore, we can do the following:

SELECT COALESCE( null, 'Horse' );

Result:

Horse

But the difference is that we can pass more than two arguments if we wish:

SELECT COALESCE( null, null, null, 'Cat', 'Horse' );

Result:

Cat

Either way, it simply returns the first non-null value.

NoSQL Databases

The whole concept of IFNULL() isn’t limited to just SQL databases. Other DBMS types such as NoSQL databases also provide similar functionality.

For example, MongoDB has an $ifNull function that does pretty much the same thing.