> The same applies with two people known to have no middle name—where you store a zero length string.

I think that's the thing. People use NULL to mean empty value, instead of unknown value.

Not necessarily for strings because there's the empty string, but for numeric fields, including foreign keys

Admittedly non-string fields do make me wish for two kinds of NULL so I can differentiate between zero, confirmed-nothing and unknown. But it's never been an issue for me in practice.

Except, Oracle treats zero length string as null.

That's just stupid

Seriously? That's the sort of thing I'd expect to find in MySQL 3-point-something, not Oracle.

Yes. It's horrible. But presumably it's a decades-old "feature" retained for backward compatibility…

  SELECT 'foo' WHERE bar = baz FROM quux
  UNION ALL
  SELECT 'bar' WHERE bar <> baz FROM quux

I would understand returning an error in that case if bar or baz were unknown. The truthiness of bar = baz is unknown, so that's an error.

But that's not what SQL Servers do. They're all pedantic about how NULL means unknown right up until the rubber hits the road of the WHERE clause, when suddenly NULL means false.

Which means that they're not saying that foo = bar is unknown, they're instead making a definitive assertion that both foo = bar is false and foo <> bar is false. And that's disastrously wrong.

Hard disagree. It should be impossible to make any truthful claim about a NULL with respect to any other value including NULL. Thus it is correct that both foo = bar is false and foo <> bar is false.

Were it any other way, joins wouldn’t work properly.

...except that NULL is still conflating the two concepts of "not known to exist" and "known to not exist" (I know some people who would argue for empty strings to be bad style).

Especially for (composite) unique indexes on NULLable foreign keys, where NULL signifies the non-existence of a different table entry (if you don't want to resort to a special "not existing" entry in that table), this behavior can be surprising and definitely got me a few times.

I disagree, because while I agree there are multiple conflated concepts within NULL (which can be rehabilitated in the schema if necessary) in practice it works correctly either way.

If two different products have the price of $0 and therefore free, their prices are equal.

If two different products both have prices which are "not known", their unknown prices are not equal.

If two different products both have prices which are "known to not exist" their undefined prices are also not equal.

The problem is that "Unknown" is so much less useful than None. Date of Death is "none" is much more of a useful concept when I'm building a database of people than Date of Death is "unknown". I have a lot more alive people in my database than we-don't-know-if-they're-dead people.

If SQL servers had algebraic types and I could roll my own "Maybe", then this bizarre decision would work. But they don't, so it doesn't.

Null can mean whatever you want it to mean. It can mean not dead yet if you want. And the logic of NULL works in that case too—two people who haven’t died yet don’t have equal dates of death.

The problem with having a value called “none” is that it would be tempting to make none = none return true, which is madness.

(I do agree it would be nice if we could have multiple kinds of null instead of just one. But I would want them all to have the same equality properties as NULL.)

Let's check out SQL's "acknowledged lack of data" in a case statement:

    create table test(a bool);
    insert into test(a) values (NULL);
    select case a when a then true else false end from test;

The answer should be NULL, right?

No, the answer must be false.

Since null = null is false, your case statement will return the else value. You hard coded it to be false, therefore it will return false.

Yes, I know what it will be, but it isn't really acknowledging the lack of data, is it? It turns NULL input data into a 100% false result.

You’re the one who wrote the logic; YOUR code is what has forcibly collapsed the null to false. It’s absurd for you to write code and then complain when it works exactly as written.

If you want the lack of data to be acknowledged in a case statement, it’s up to you to handle it. SQL isn’t a magical fairy that does what you want without asking.

People generally expect equality to produce either true or false. Not a tri-value pair.

That interpretation makes sense to me when I read the docs, but is way too easy to miss when I'm actually doing comparisons.

You should always think about NULL state for every field when designing your schema. If you don't have a clear sense of what the NULL state shall mean for a given field, the field should not allow null values.

For example: a shopping cart might contain a tee-shirt in blue colour and expedited shipping in NULL colour. That blue tee-shirt might be the same colour as some blue socks... but that NULL-coloured shipping isn't the same colour as a NULL-coloured charitable donation.

For this you can make any field non nullable and offer a default value for the field.

Counter example: SAP HANA doesn't support NULLs, and as a result if I see 0 in any column in the database I have to wonder was it 0 that user submitted or 0 that system added as a placeholder. I would need to go back to documentation and see if the field is mandatory etc. Better have it explicitly stored in the data itself.

Yes, it's perfectly sensible; the only problem is with the name used. If it was called `UNKNOWN` or something instead, nobody would be complaining.