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 |