> NULL only has one meaning: NULL. This is roughly analogous to unknown.
Which is what I'm arguing against, because it's used in two unrelated ways in SQL -- as a data value, and to express no matching row found in a JOIN. So no matter how it's defined formally, in practice it has two meanings that have nothing whatsoever to do with each other. One is a value and can be stored, the other says 'not found' and results only from expressions and isn't for storage.
> but you can infer the difference
Yes, as I said there are lots of workarounds. But they're still workarounds.
> This is one of the reasons some purists argue against NULL existing in SQL at all
Sure, but the reality is that NULL is used in a practical sense to mean "no data entered". It's so ridiculously common that only some columns have no data for a particular row, and so e.g. you need the 'time_finished' column to either have a valid date, or it's NULL if the activity hasn't finished yet. The alternative is to have an additional boolean column 'is_finished' and for 'time_finished' to be arbitrarily '1970-01-01T00:00:00Z' whenever is_finished is false, which is clunky and redundant.
Purists can argue what they want, but NULL is so ridiculously useful as a stored data value it doesn't really matter.
> you need the 'time_finished' column to either have a valid date, or it's NULL if the activity hasn't finished yet
Rust’s Diesel has an interesting way of dealing with this. “None” means missing while “Some(None)” means the value null. So when updating a record, “None” makes no change while “Some(None)” sets the value to null.
This distinction is obviously lost when retrieving from the database, but it’s an interesting concept.
But in classic Javascript style, it's half-assed. Being able to assign anything to undefined makes the distinction between null and undefined pointless.
Which is what I'm arguing against, because it's used in two unrelated ways in SQL -- as a data value, and to express no matching row found in a JOIN. So no matter how it's defined formally, in practice it has two meanings that have nothing whatsoever to do with each other. One is a value and can be stored, the other says 'not found' and results only from expressions and isn't for storage.
> but you can infer the difference
Yes, as I said there are lots of workarounds. But they're still workarounds.
> This is one of the reasons some purists argue against NULL existing in SQL at all
Sure, but the reality is that NULL is used in a practical sense to mean "no data entered". It's so ridiculously common that only some columns have no data for a particular row, and so e.g. you need the 'time_finished' column to either have a valid date, or it's NULL if the activity hasn't finished yet. The alternative is to have an additional boolean column 'is_finished' and for 'time_finished' to be arbitrarily '1970-01-01T00:00:00Z' whenever is_finished is false, which is clunky and redundant.
Purists can argue what they want, but NULL is so ridiculously useful as a stored data value it doesn't really matter.