09:48

Comparing NULLs Considered Silly

17 December 2010

tl;dr: You can’t compare NULLs. A nullable primary key is a contradiction in terms. You can’t join on NULL, so a NULL foreign key refers to nothing, by definition. NULL doesn’t do what you think it does, no matter what you think it does.

---

NULL in SQL is annoyingly complex.

There’s really no conceptual model of NULL that will not end up surprisingly you in unpleasant ways. Jeff Davis, last year, wrote a great blog post that, if could be so bold, could be paraphrased as “conceptual models of NULL considered harmful.”

Thus, it’s not surprising that some… well, surprising ideas about NULL sometimes pop up.

Recently, on the Django developers’ list, the phrase “nullable primary key” caught my eye. This inspired me to write these thoughts about NULL, and in particular NULL being used as keys.

First:

It ie meaningless to compare two NULL values.

I’ve noticed application programmers often treat NULL as a magic value that any type can possess (I’ve been quite guilty of this, too). While this is somewhat true, it’s also a dangerous path to go down, because:

NULL = NULL

… is NULL, not true. Whatever else you can say about NULL, a NULL value means you can make no claims about what value it is. Saying, “I have no idea what this value is, and I have no idea about what that value is, but are they equal?” is, I would hope, pretty self-evidently meaningless.

Now, this immediately implies:

You cannot join on NULL.

If a foreign key column is NULL, you can’t do an inner join on it to another table, even if key column(s) being referred to is NULL. This follows directly from the fact that you can’t compare NULL values; joining is just built around comparison, after all.

Yes, you can do things like:

SELECT a.*
    FROM a
    INNER JOIN b
        ON (b.col = a.col) OR ( (b.col IS NULL) AND (a.col IS NULL) )

Setting aside that you’ve pretty much committed yourself to a nested loop at this point (and thus a very expensive operation), the fact that you have to jump through this hoop should be an indication that the wrong path is being trod.

So, please remember: NULL in a foreign key field does not mean “This refers to rows in the other table that have a matching NULL,” because there’s no such thing as a “matching NULL.”

Moving on to primary keys:

A primary key is a combination of columns whose values, taken together, uniquely specify a row.

Thus, a nullable primary key is equally meaningless, as the whole point of a primary key is for it it to be compared to other values to determine uniqueness. (The SQL standard prohibits NULLs in primary key columns, so it’s not just a good idea, it’s the law, or at least the recommendation.)

---

The SQL standard calls for NULL to be thrown up in places where it really should require an error. For example:

SELECT SUM(col) FROM t WHERE FALSE

… returns NULL, as the result of any aggregate function over zero rows is NULL. But the sum of no numbers is 0, not “unspecified” (or whatever you want to call NULL).

Worse:

SELECT AVG(col) FROM t WHERE FALSE

is NULL, while:

SELECT 0/0

… much more rationally gives a divsion-by-zero error.

My guess is that the SQL standards committee is loathe to have the spec require errors for more-or-less common operations, and that’s where a lot of the stranger cases of NULL come from, as a way of having a normal-but-flagged return from an edge case.

It’s really a shame that NULL is so complex and counterintuitive, but there’s really no hope for it except to learn the rules, and not try to abuse NULL to do things it wasn’t designed for.

There are 3 comments.