Crazy NULLs in MySQL

May 27, 2009

The topic of MySQL and NULLs came up in my IRC room today. It turns out that MySQL tends to handle NULLs in a rather wonky way at times. Personally, I usually do not like allowing NULLs, and the only time they are ever present would be in a RIGHT or LEFT JOIN that had missing information. There are many people out there, however, that do use NULLs, and love them. I am not knocking the use of NULLs in any way, but before you sit down and use NULLs again be sure you take some time and study this bit of trivia one of the guys in the IRC showed us.

 mysql> select if(null=null,"yep","nope");
 | if(null=null,"yep","nope") |
| nope                       |

If you think this is strange behavior you are right. According to the MySQL manual on control flow the correct response should be ‘yep’.