MySQL: Number + NULL

Maybe not breaking news, but I think it’s interesting enough of a point, and I didn’t really find anything about the topic when I googled it. If you do any addition, subtraction, multiplication, or division (and probably a lot more mathematical functions for that matter) and NULL is one of your values, the entire expression will evaluate to NULL.

For example, this statement returns NULL:

mysql> select 4 + NULL;

+----------+
| 4 + NULL |
+----------+
|     NULL |
+----------+

Normally you wouldn’t do the above in such a simple way, for instance, you might do some addition in a subquery. For example,

mysql> select 4 + (select val from table1 WHERE id < 3 LIMIT 1);

+---------------------------------------------------+
| 4 + (select val from table1 WHERE id < 3 LIMIT 1) |
+---------------------------------------------------+
|                                              NULL |
+---------------------------------------------------+

This statement will return NULL

Fortunately, MySQL includes the handy IFNULL operator. IFNULL lets you specify a value if the expression evaluates to NULL. Example:

mysql> select 4 + IFNULL(NULL,0);
+--------------------+
| 4 + IFNULL(NULL,0) |
+--------------------+
|                  4 |
+--------------------+

As a result, you can now do addition in subqueries without getting weird NULL results. Modifying our practical example, we can now use:

mysql> select 4 + IFNULL((select val from table1 WHERE id < 3 LIMIT 1),0);

+-------------------------------------------------------------+
| 4 + IFNULL((select val from table1 WHERE id < 3 LIMIT 1),0) |
+-------------------------------------------------------------+
|                                                           4 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

without fear that we wil get a NULL answer.

As a subnote, if you wanted to try this on your own, I just created an empty table:

mysql> create table table1(id int, val int);