Comparing NULL values in MySQL
In another post I explained that I learned how MySQL deals with trailing spaces in String comparisons and that in some cases spaces are just ignored. Today, I want to cover another element of MySQL queries that might cause some problems for people new to this database: How MySQL treats NULL values.
The logic MySQL applies for NULL values seems a bit odd when you first encounter it, but it does make sense, when you take into account that from a MySQL point-of-view NULL means unknown. It is impossible to compare something unknown with something else, so these kinds of comparisons always return NULL, which resolves to false in MySQL.
This does not apply only to the operators mentioned above, but to all arithmetic comparison operators. For the reasons mentioned above, it is important to always check for NULL values explicitly if NULL is important using the IS NULL and IS NOT NULL conditions.
| Condition | Result |
|---|---|
'a' NOT LIKE NULL |
false |
NULL = '' |
false |
NULL <> '' |
false |