'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);