Q> Ah, so it’s because NULL is not a value, but nothing?
Kris> Nothing is not the right term. It’s NULL. That has many meanings in SQL, it is not even consistent. It is also not None, null, nil, or undef in your host language. It is NULL. And what happens in detail you have to know.
Kris> This happens, because the UNIQUE INDEX checks if a currently existing value = the new value for this column. If true, the value is rejected. But comparing NULL to NULL is NULL for any operation. It’s neither TRUE nor FALSE, it’s NULL. Comparing any other value to NULL is also NULL, for any comparison operator.
Kris> Perl developers know undef and see
Kris> But SQL does
Kris> NULL is NULL, it is not Nothing or undef. It is not 0, and not “”.
Kris> Perl developers again:
Kris> Check SQL:
Kris> So does NULL destroy everything it comes into contac with? Nope, it’s not systematically correct. In aggregates, it is skipped.
Kris> So avg(t) is defined as sum(t)/count(t) = 3/2 = 1.5
Kris> To have a predicate function you can test with, you get ISNULL(). And an Operator, IS NULL.
Kris> Then there is a nonstandard MySQL comparator, the spaceship, which normalizes NULL:
Kris> Compare to a regular equality:
Kris> And there is a variadic function which returns the leftmost non-NULL value, for providing defaults.
Kris> So we ask our developers to not use NULL values, or provide an explaination in business logic terms what exactly a NULL in a column means. If a query can yield NULL values, there must be a COALESCE().
Kris> Developers fail at NULL. If for tt, we ask for
what is the rest of the table? Which SQL-Statement would show be the other half of the universe?
Kris> But stuff is missing!
The opposite of SELECT * from tt where t = 1 is precisely not select *
from tt where t <> 1. It is SELECT * from tt where t <> 1 OR t IS NULL,
since tt.t is nullable. And because developers never get this right we try to discourage them from using these.