MySQL: Number + NULL = NULL
September 20, 2006 – 3:16 pmMaybe 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);
14 Responses to “MySQL: Number + NULL = NULL”
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!
By Xaprb on Sep 20, 2006
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
By jon on Sep 20, 2006
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 …
By Hartmut Holzgraefe on Sep 21, 2006
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.
By jon on Sep 21, 2006
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)
By Sheeri on Sep 21, 2006
Sheeri: Mine is a practical real world example. I realize that there can be multiple solutions to a given problem. Thanks.
By jon on Sep 21, 2006
And sheeri, you are wrong anyway.
mysql> select concat(4,5);
+————-+
| concat(4,5) |
+————-+
| 45 |
+————-+
By jon on Sep 21, 2006
It appears that NULL does not work this way in PHP, however.
By Brandon Drury on Nov 30, 2006
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.
By IJay on Feb 20, 2007
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.
By Mark on Mar 14, 2007
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
By Julius on May 6, 2007
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.
By JTW on Jun 8, 2007
Thanx for writting this, you nearly saved my ass !
By Stabbquadd on Jul 16, 2007
Very helpful, thanks a lot
By Jihad on Apr 11, 2008