NULLIF versus IFNULL

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

Care has to be taken when using the NULLIF and IFNULL functions.

IFNULL

IFNULL function tests for a NULL and then returns the second parameter. Here is an example:

mysql> SELECT IFNULL(NULL, 1);
+-----------------+
| ifnull(null, 1) |
+-----------------+
|               1 | 
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(3, 1);
+--------------+
| ifnull(3, 1) |
+--------------+
|            3 | 
+--------------+
1 row in set (0.00 sec)

In the first example we can see that the first parameter is definitely a NULL value, hence, the second parameter is returned. The function COALESCE is very similar to the IFNULL function except the IFNULL limits the number of checks to one parameter, whereas COALESCE can accept multiple parameters returning the first non-NULL as follows:

mysql> select COALESCE(null, 1);
+-------------------+
| COALESCE(null, 1) |
+-------------------+
|                 1 | 
+-------------------+
1 row in set (0.00 sec)

mysql> select COALESCE(null, null, 4);
+-------------------------+
| COALESCE(null, null, 4) |
+-------------------------+
|                       4 | 
+-------------------------+
1 row in set (0.00 sec)

NULLIF

The NULLIF function compares two parameters and returns a NULL if both match, otherwise the first parameter is returned.

We have included some examples below:

mysql> select nullif(null, null);
+--------------------+
| nullif(null, null) |
+--------------------+
| NULL               | 
+--------------------+
1 row in set (0.00 sec)

mysql> select nullif(null, 1);
+-----------------+
| nullif(null, 1) |
+-----------------+
| NULL            | 
+-----------------+
1 row in set (0.00 sec)


mysql> select nullif(1, null);
+-----------------+
| nullif(1, null) |
+-----------------+
|               1 | 
+-----------------+
1 row in set (0.00 sec)

mysql> select nullif(1, 1);
+--------------+
| nullif(1, 1) |
+--------------+
|         NULL | 
+--------------+
1 row in set (0.00 sec)

mysql> select nullif(2, 1);
+--------------+
| nullif(2, 1) |
+--------------+
|            2 | 
+--------------+
1 row in set (0.00 sec)