MySQL – IFNULL(), NVL(), ISNULL(), NULLIF() and COALESCE()

[adsense id=”0514458240″ width=”468″ height=”60″]

MySQL offers two methods for determining a NULL value and replacing it with another. In SQL Server the function IFNULL is available and in Oracle NVL. However, standard standard SQL shows us that the COALESCE function is standard ANSI SQL and would standardize your SQL code.

The syntax for COALESCE is:

COALESCE (field, field [, field] ...) 

The coalesce scans through each of the fields until the first non-null is encountered and returned. If no non-null fields are encountered then a NULL is returned.

mysql> select * from testtable;
+----+------+----------+----------+
| id | name | lastname | deptname |
+----+------+----------+----------+
|  1 | NULL | NULL     | Accounts | 
|  2 | NULL | Kelly    | NULL     | 
|  3 | Tom  | NULL     | NULL     | 
+----+------+----------+----------+
3 rows in set (0.00 sec)

mysql> select coalesce(name, lastname) from testtable;
+--------------------------+
| coalesce(name, lastname) |
+--------------------------+
| NULL                     | 
| Kelly                    | 
| Tom                      | 
+--------------------------+
3 rows in set (0.00 sec)

mysql> select coalesce(name, lastname, deptname) from testtable;
+------------------------------------+
| coalesce(name, lastname, deptname) |
+------------------------------------+
| Accounts                           | 
| Kelly                              | 
| Tom                                | 
+------------------------------------+
3 rows in set (0.00 sec)

Here is a list of the native SQL calls for each of the databases and those that support COALESCE.