Problem with NULL in MySQL

One fine morning you simply login to MySQL Terminal and…
[email protected]:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.1.37-1ubuntu5.1 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select concat('Hello ',NULL,'World!');
+--------------------------------+
| concat('Hello ',NULL,'World!') |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select 1 + NULL + 3;
+--------------+
| 1 + NULL + 3 |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)
mysql>

WHAT THE ####???
This is unexpected. Normally one would expect concatenating a NULL with string will simply result in string. But MySQL gives you NULL. What???
Look here-> http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html.
No fear. We have a solution. :-). We will use IFNULL(). Have a look.
mysql> select concat('Hello ',IFNULL(NULL,''),'World!');
+-------------------------------------------+
| concat('Hello ',IFNULL(NULL,''),'World!') |
+-------------------------------------------+
| Hello World! |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1 + IFNULL(NULL, 0) + 3;
+-------------------------+
| 1 + IFNULL(NULL, 0) + 3 |
+-------------------------+
| 4 |
+-------------------------+
1 row in set (0.00 sec)

One may also opt for COALESCE().

Author: Kumar Chetan Sharma

Run of the mill developer.

2 thoughts on “Problem with NULL in MySQL”

Comments are closed.