Home / Funtions and Operators / Question No: 236

Functions intended specifically for use with NULL values include ISNULL() and IFNULL().  ISNULL() is true if its argument is NULL and false otherwise:

mysql> SELECT ISNULL(NULL), ISNULL(0), ISNULL(1);
+--------------+-----------+-----------+
| ISNULL(NULL) | ISNULL(0) | ISNULL(1) |
+--------------+-----------+-----------+
|            1 |         0 |         0 |
+--------------+-----------+-----------+

IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the function returns its second argument:

mysql> SELECT IFNULL(NULL,'a'), IFNULL(0,'b');
+------------------+---------------+
| IFNULL(NULL,'a') | IFNULL(0,'b') |
+------------------+---------------+
| a                | 0             |
+------------------+---------------+

Other functions handle NULL values in various ways, so you have to know how a given function behaves. In many cases, passing a NULL value to a function results in a NULL return value. For example, any NULL argument passed to CONCAT() causes it to return NULL:

mysql> SELECT CONCAT('a','b'), CONCAT('a',NULL,'b');
+-----------------+----------------------+
| CONCAT('a','b') | CONCAT('a',NULL,'b') |
+-----------------+----------------------+
| ab              | NULL                 |
+-----------------+----------------------+

But not all functions behave that way. CONCAT_WS() (concatenate with separator) simply ignores NULL arguments entirely:

mysql> SELECT CONCAT_WS('/','a','b'), CONCAT_WS('/','a',NULL,'b');
+------------------------+-----------------------------+
| CONCAT_WS('/','a','b') | CONCAT_WS('/','a',NULL,'b') |
+------------------------+-----------------------------+
| a/b                    | a/b                         |
+------------------------+-----------------------------+