Many programming languages contain the concept of a null “value,” which can mean “nothing,” or may be precisely equal to integer zero. When encountering this in a database, it would be natural to assume it means roughly “nothing.” This is a naive, although workable definition of null. As we’ve seen, simplified and even incorrect definitions can nonetheless sometimes be helpful. In this case, it is better to have absolute clarity.
Null can confuse with its subtleties. For example, in Java it is taken for granted that the expression (null == null) will return true. Nothing equals nothing, right? However, the following MySQL query does not work as expected:
SELECT * FROM `users` WHERE `password` = NULL;
This will always return no rows regardless of the contents of the table. What happened?
In SQL, null isn’t a value — it’s the condition of having no value. In Java, it is important to understand the distinction between null and an empty string, if only to know they are not equal. Likewise, it is important to know that in MySQL, any comparison operation with NULL will always return false, even with itself. That is to say that NULL is not reflexive.
To drive this point home, consider the following test query in MySQL:
mysql> SELECT IF(1 = 1, ‘true’, ‘false’);
This returns ‘true’ if the statement evaluates to true. Now let’s consider this statement:
mysql> SELECT IF(NULL = NULL, ‘true’, ‘false’);
This demonstrates that NULL is not reflexive. NULL does not equal anything, not even itself. Let this sink in. You should now understand why `password` = NULL always returns the null set.
Additionally, an operation that contains NULL always produces a NULL result, unless otherwise indicated in documentation. All columns in the following example return NULL:
mysql> SELECT NULL, 1+NULL, CONCAT(‘Some String’, NULL);
NULL | NULL | NULL
To check for the nullity of a cell, use the operators IS NULL, IS NOT NULL, or the IFNULL() function.
A NULL cell in a database should indicate that the value is unknown. An empty string should indicate that the value is blank. This is confusing because Java and other programming languages do not have such a distinction (not as a design flaw of course, but because programming languages are not databases).
Now go fix your database.