4 ms·

...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

...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.

sjwright 7y ago
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.
- Pxtl 7y ago
  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.
  - sjwright 7y ago
    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.)

sjwright 7y ago

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.

Pxtl 7y ago
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.
- sjwright 7y ago
  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.)