Rusty Razor Blade
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:
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,
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:
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:
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:
create table table1(id int, val int);
If you enjoyed this article, please consider sharing it!
Other DBs have similar functions to IFNULL(), such as MS SQL Server’s ISNULL(). But these are not portable. COALESCE() does the same thing in this case, and is part of standard SQL, so I like to use it when I can. There are sometimes subtle differences between the proprietary functions and COALESCE(), so it’s not something you’d want to do search-and-replace on.
Thanks for writing this!
Xaprb: Yes, this is true. It looks like it’s a little bit more flexible, in that you could specify multiple subqueries, and it would take the first non-null value.
“Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.” from the mysql docs.
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
Just for the records: the NULL result is expected behavior and not MySQL specific, the result of adding some unknown ammount to some number is unknown …
Hartmut: I wasn’t suggesting it was a bug (or mysql specific), just not something I expected when I first encountered it. If my post helps someone spend less time trying to figure it out, then I’m happy.
1) CONCAT has the same functionality
2) You do not need a table to demonstrate either point
mysql> select 1+NULL+1;
+———-+
| 1+NULL+1 |
+———-+
| NULL |
+———-+
1 row in set (0.00 sec)
mysql> select concat(‘a’,'b’);
+—————–+
| concat(‘a’,'b’) |
+—————–+
| ab |
+—————–+
1 row in set (0.00 sec)
mysql> select concat(‘a’,NULL,’b');
+———————-+
| concat(‘a’,NULL,’b') |
+———————-+
| |
+———————-+
1 row in set (0.00 sec)
Sheeri: Mine is a practical real world example. I realize that there can be multiple solutions to a given problem. Thanks.
And sheeri, you are wrong anyway.
mysql> select concat(4,5);
+————-+
| concat(4,5) |
+————-+
| 45 |
+————-+
It appears that NULL does not work this way in PHP, however.
Just found this in Google. Thank you for that article. I just wish I would have found this earlier. Would have saved me some hours. I had the same problem (“Why the heck is 10 + NULL not 10?”). Now I’ve got a good solution. Thanks.
Thanks for this. I was having the same problem. Maybe should have realized it, but it makes me feel good that I’m the first to be bit. Also Xaprb, thanks for COALESCE() – I never really understood what it would be good for.
Just spend almost an hour trying to figure out what to do in case my subquery returned null – found your IFNULL() solution on google, and it solves my problem perfectly! Thanks
1. some expressions like GREATEST() return different results on different versions of MySQL.
SELECT GREATEST(0,NULL); returns 0 on 4.1 and NULL on 5.0.
2. to CONCAT with NULL values, use CONCAT_WS(”, …). The first parameter is a delimiter and in this case, it’s just an empty string.
Thanx for writting this, you nearly saved my ass !
Very helpful, thanks a lot