Happy to hear Darwen dislikes 3-value logic. A language where x=x can return something equivalent to false in a common use-case is wrong.
Then almost all common languages are wrong, if you consider floating point NaNs to be a “common use-case” - and surely they must be, since it is easy to produce them using ordinary arithmetic operations.
I work on a lot of code that uses SQL NULLs, but most of the time I don’t encounter them directly, because I’m using some ORM which translates them to Java nulls (or Go nil or whatever), and many query generation frameworks handle the weirdness around querying for nulls (IS NULL vs = NULL) automatically. Most programming languages don’t give their nulls the weird equality semantics that SQL nulls have
Ahh, every time this conversation about how SQL Nulls violate reflexive property of equality, people are like "so do NaNs" and I'm wondering "who are these people who are dealing with NaNs all the time?".
I'm mostly a back-end and DevOps guy, so I don't write that much JS. I didn't know that JS used NaN the way other languages would use Null or exceptions for numerical operations.
...
Also that's terrible.
Actually you give the answer for attribute not applicable - and you don't get a thousand different subcases. The measure of goodness is does your db model fit the real world data.
But at the same time, proper languages that have operator overriding and custom types make that kind of thing trivial - look at how many languages have a Maybe<T> these days, which is basically automating that process.
The fact that doing it in SQL would be suicidal shows that, while SQL was a brilliant language for the '70s... it has not evolved nearly enough.
– a missing value (existing but unknown), for example a person's birth date that happens to be unknown
– the non-applicability of a value, for example the spouse of a person who never married
– a combination of the above, for example the death date of a person where it is unknown whether they have died yet (but if it was known that they are alive, the death date would be NULL all the same)
– an empty set (see for example https://dbfiddle.uk/UJJgVCZ_)
Depending on the context in an SQL expression, NULL sometimes behaves like an unknown value (propagates upwards in expressions), sometimes like a non-value (doesn't match any conditions), sometimes like an empty set. This makes it unintuitive and difficult to reason about.
You are asking about what else to use. In principle you could use placeholder values (like using the date value 9999-12-31 to mean "hasn't died yet"), but that has its issues as well. The bottom line is, there is really no good alternative in SQL. And that's what people dislike about SQL. Because you could imagine a database language with a standard NULL placeholder value with consistent and straightforward semantics.
Various SQL servers have accepted the need to support stuffing multiple data-types into a single column - MSSQL has SQL_VARIANT, Orcale has ANYDATA, SQLLITE has... well, everything in SQLLITE. So between that and the existence of NULL, the ship has already sailed. May as well make it formal and schema-defined behavior using tagged unions.
As the rest of the software industry has figured out, `Maybe<T>`s are better than NULLs.
Similarly, in a database language the column/field references would be typed as optional or non-optional, and you are only allowed to perform value-related operations on non-optional references. And an optional reference can be made non-optional by first passing the data to a condition equivalent to SQL's `IS NOT NULL`, for example. Or for operations that can support NULL (like maybe sorting), those would have a different form for supporting NULLs. For example, plain `ORDER BY` would cause a parse-time error when applied to a nullable column where NULL has not been filtered away, but `ORDER BY ... NULLS FIRST` (or `LAST`) would be applicable to nullable columns. Similarly for grouping, there would be options if you do want to include a NULL group, but by default it wouldn't type-check.
But you wouldn't get the nonintuitive behavior of SQL where NULLs sometimes act like NaNs, for example `NULL not in ('foo', 'bar')` evaluating to false, and `NULL in (NULL, 'foo', 'bar')` also evaluating to false.