[knorker]

Not parent commenter, but to me it seems "obviously correct".

As explained in the article NULL means "unknown". Let's say I have two people, and a "tax identifier" column. Let's say two people can't have the same tax id. Obviously "don't have one" or "unknown" fit very well in NULL, and two people can both be missing a tax id, even if those who have it need it to be unique.

[chris_wot]

Except that's the ambiguity of NULL in the standard. It doesn't just mean "unknown". It can also mean "no value". And you can clearly compare two elements with no value.

Which is why nulls are so incredibly confusing.

[knorker]

Yeah that's true. All the database can say is "I don't have a value here". Not "there exists no value for this column".

But this doesn't mean that two NULLs are the same. Two people with NULL tax IDs are not the same (one turns out to be unknown but existing, the other is a toddler without a tax ID). Ten people with NULL address don't live in the same house. Half declined to give address, the other half are homeless. Either way we can't send mail to them.

If you use the database to have a UNIQUE constraint of "only one purchase per household" then it makes no sense to allow NULL addresses, but only allow the first NULL address purchaser to buy the thing.

Or "sorry, in this hotel we only allow one guest at a time without a car, and someone else already doesn't have a car".

Does that guest without a car actually have a car? I don't think that's something that the database can solve.

Should databases have two separate values for unknown or no value? That sounds like a world of hurt, with two types of null.

[jl6]

> Should databases have two separate values for unknown or no value? That sounds like a world of hurt, with two types of null.

It’s also a non-problem in practice, because if an application needs to distinguish between multiple types of nulls, it can very easily just use an extra column holding the information needed to disambiguate.

[jstanley]

Similarly, the need for non-boolean columns is a non-problem in practice, because an application can just make an extra column for every bit it needs to store. If it needs strings or arbitrary-width numbers it can just join on other tables that store those bits.

(Existence of a workaround doesn't turn a problem into a non-problem)

[jl6]

I stand by it being a non-problem in practice because the workaround is trivial, and the supposed solution adds complexity everywhere.

There isn’t even a universally agreed methodology for how to handle nulls in the relational model in theory.

It’s a semantic problem that can only be solved with reference to the requirements of a particular use case, and when you do that using explicit extra columns, the problem evaporates.

[fendy3002]

Which happen in world of javascript. It has null and undefined. Null variable is a variable / property that's defined and set with null, while undefined is a variable / property that's not defined yet (or maybe not initialized, I forgot).

I hope at least SQL will give another comparison operation, such as the current equal sign (=) means a.prop is not null && b.prop is not null && a.prop equal b.prop (for not defined yet).

Let's say another sign like a.prop *= b.prop, meaning a.prop is null && b.prop is null || a.prop = b.prop (for our intention is both value are empty).

[pdw]

Such a comparison operator exists: IS (NOT) DISTINCT FROM.

[jhgb]

> It doesn't just mean "unknown". It can also mean "no value".

Isn't the canonical representation of known-no-value an absence of a tuple? Like as opposed to saying "There exists an employee X who works in department NULL", you simply don't make any claim about employee X working in a department? After all, when enumerating the members of a set, you're also omitting the enumeration of non-members of a set, and the law of excluded middle applies.

[Tostino]

Nulls will reappear in your queries due to joins even if none exists in your data model.

[jhgb]

Ah yes, they're the curse of SQL. Still doesn't seem to be a reason to not squash them out as much as feasible within the constraints of SQL.