Databases: Null is not a value

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’);
true

This returns ‘true’ if the statement evaluates to true. Now let’s consider this statement:

mysql> SELECT IF(NULL = NULL, ‘true’, ‘false’);
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.

4 thoughts on “Databases: Null is not a value”

    1. Having briefly looked over the wikipedia article for option types minutes before, I will now answer as a Foremost Expert in Type Systems:

      Well, if your type system has the concept of a null “value” — distinct from a null object — and that is also distinct from the value 0, then feel free to use it to your heart’s content to represent an unknown! But from a correctness perspective this isn’t much different from using an extra bit to track knowingness (or using some clever convention to save the extra bit altogether). I would bet that’s how this is implemented anyway, albeit it may have some fancy language-level atomicity guarantees or something, otherwise its more or less syntactical sugar.

      My point was that null means different things in Java/C++ and an RDBMS such as MySQL or Postgres. Nullity in a DB provides you with (literally) an extra bit to store useful information, whereas null in a classic OO language is either equivalent to 0 or represents an object that has not yet been constructed, so you cannot safely use it to denote an unknown value. Likewise, it would be improper to treat a MySQL NULL equal to 0 or “” in the same way that you shouldn’t try to assign 4 = 5.

  1. Peter,

    I really believe you’ve gotten some important things wrong in this post. One of them is that NULL is not a value. I can deploy a couple of arguments that I think will convince you otherwise. But let’s focus on this statement you made:

    “Any comparison operation with NULL will always return FALSE.”

    Try this experiment:
    a. Create a table that has a nullable DateOfBirth column and a check constraint with the condition DateOfBirth >= ‘1980-01-01’.
    b. Do an INSERT into that table with a NULL DateOfBirth.
    c. Did the INSERT succeed or fail?

    If your statement above is correct, the expression will evaluate to False and the INSERT will fail. If the INSERT succeeds, your statement is incorrect. Satisfy yourself that you agree with that reasoning and then try the INSERT.

    1. Hi Brian! You are right, and I think part of the problem is that I was plainly wrong in one respect, and misleading in another. What I probably should have said was:

      Any comparison operation with NULL will be NULL, which is false-y.

      To my knowledge, this is correct, and explains why NULL=NULL is not true, but neither is it strictly false. The equality of this expression is unknown. In ternary logic, this is intuitive if we allow null to take on any arbitrary value, since it is by definition indeterminate.

      With this in mind, I think I can succinctly run your experiment (in Postgres, since MySQL doesn’t support check constraints) like this:

      SELECT NULL > 1;

      The result is NULL, irrespective of what is on the RHS. A check constraint would thus be considered satisfied, since check constraints in Postgres don’t fail on NULLs, but rather only when the expression is strictly false (see http://www.postgresql.org/docs/9.4/static/ddl-constraints.html). This is one case in Postgres where the truth value of NULL is not false, but there aren’t any exceptions in MySQL.

Leave a Reply

Your email address will not be published. Required fields are marked *