Hacker News Reader

Comment by the_mitsuhiko | Hacker News Reader

Because it represents (and behaves) as unknown and not as absent.

Practical example is `1 not in (null)`

Just my 2c from SQL standards perspective:

> “Every [SQL] data type includes a special value, called the null value,” “that is used to indicate the absence of any data value”.

(http://modern-sql.com/concept/null)

For the Boolean type, null = unknown:

> Note that the truth value unknown is indistinguishable from the null for the Boolean type. Otherwise, the Boolean type would have four logical values.

(http://modern-sql.com/concept/three-valued-logic)

Although they are very closely related, I believe the general `null` value (for any data type) and three-valued logic (null for Boolean) should be separated (that's why I have two articles).

> Because it represents (and behaves) as unknown and not as absent.

It can represent both. For example, in a table of biographical data, a NULL value for DateOfDeath could represent N/A for someone still living, unknown for a dead person whose date of death is not known, and total uncertainty for a missing person who could be alive or dead.

I've seen this design in the wild quite often. It's most often attributed to the abuse of an intended original design. The original designer most likely intended the value populated in the "DateofDeath" column to imply death. The intention didn't account for someone down the system's evolution to imply "ALLOW NULL" to be interpreted as optional.

If your table is expected to answer "how many dead people whose date of death is unknown" then you'll most certainly also need an "IsDead" (true/false) NOT NULL column. (Best separate the table into "Death" with NOT NULL DateofDeath.)

Operational concerns might push you otherwise, but I think in terms of correctly representing the world what makes the most sense is a separate table of deaths with a primary key that's also a foreign key into the table of people, and which has a nullable date of death. That lets you represent all three of "they are living", "they are dead but we don't know when they died", and "they are dead and we know when they died".

I agree with the first part of your statement:

"Because it represents (and behaves) as unknown".

But I'm also inclined to think of the unknown BECAUSE of (or as a consequence of) absence of data. And most importantly, this absence of data (non-existence) is useful too.

I have a problem with the logic that intends to over fit absence/unknown to mean "it could be true or it could be false" ... or worse ... "it could be both".

If you are doing this in SQL WHERE predicates:

    (T-SQL)

    WHERE IsNull(IsHivPositive, 0) = 1

I would strongly argue to reimagine the use of that "boolean" column.

However you interpret a NULL (as "value existent but unknown", "value not existent", or "no information, i.e., value might exist or not"), you will have a hard time matching your interpretation with SQL's behaviour, no matter what the standard says. In fact, interpreting NULLs as "value existent but unknown" is the most troublesome of the three interpretations, both because of SQL's rules (for instance, select * from T where A=A is empty if A is NULL, but an existent value should be certainly equal to itself even if you don't know what it is) and due to the intrinsic computational complexity of related problems (basically, related to the intractability of recognising tautologies).

The closest to a sensible semantics that I have seen is a 2012's paper titled "On the Logic of SQL Nulls", which argues that the "correct" interpretation of NULL is "value not existent". It is a quite theoretical paper, though, and it covers only a subset of SQL.

If you want to use NULLs meaning "value exists but is unknown" I recommend that you the papers written by Hans-Joachim Klein, mostly in the '90s. His "switching semantics" provides some very interesting rules for writing queries with NULLs, which are at least guaranteed to output a subset of the correct answers wrt some reasonable definition of "correctness" (some of his results were rediscovered recently by Libkin btw).

Why would NULL equal itself? You're basically asking for SQL to special-case the Boolean logic so that if a null value is compared to itself it returns true. Who needs that?

If you assume that NULL is a placeholder for a value that exists, you must require the basic property that A=A be true, even when A is NULL, because any value equals itself. This does not mean that NULL=NULL should always be true. So, this is just to point out one of the many examples of SQL being incompatible with a "value existent, but unknown" semantics. NULLs in SQL are quite a mess.

Reply on news.ycombinator.com