3 ms·
I think the issue here is that SQL should have more "NULL variants" to express why there is no concrete value. A NULL value technically means it's unknown. An
by magicalhippo 7y ago
I think the issue here is that SQL should have more "NULL variants" to express why there is no concrete value.
A NULL value technically means it's unknown. An unknown value might be 5, hence why it's not in the result set. Some abuse NULL to mean "value doesn't exist". But a value that doesn't exist can't be 3, or 42, or any other value that's different from 5, so in that regard shouldn't be part of the result set either.
Others again abuse NULL to mean "doesn't apply". And in that case I think it makes sense to include the row in the result set. For example, if I write a query to get all people who's middle name is not "William", I'd most likely want people without middle names included.
Maybe we should have introduced NEX (non-existing) and NAP (non-applicable) as possible values in addition to NULL?
- at_a_remove 7y agoAgreed. I have, off and on, labored on a still-incomplete and largely incoherent essay on this topic. NULL is overloaded to the point of some confusion.
- tabtab 7y agoRe: I think the issue here is that SQL should have more "NULL variants" to express why there is no concrete value. No, that would muddy things in my opinion, like it did to JavaScript. Instead, have more operations/functions for dealing with them in a more "normal" way, so that we can say "WHERE x <> 5" and get results one expects. I'm not sure the syntax, and my drafts would take a lot of time to explain. To give a taste, maybe have something like "WHERE ~x <> 5" in which the tilde converts x's value to the type's default, such as a blank in the case of strings. If the different reasons for "emptiness" matter, then usually it suggests the need for a "status" column of some kind so that queries can be done on the reasons. I'd need to study domain specifics to recommend something specific.
- magicalhippo 7y agoBut that would mean you need to be aware that the column can have this property, no? Continuing with my middle name example. Say I and everyone I knew had middle names, so I write a database including a required middle name column. Later I discover not everyone has middle names, and so I need to relax the restriction. In your case, I would change the column to accept NULLs, and I'd have to remember to go over my query to add the ~ operator. In my case, I'd change the column to accept NAPs (or whatever) and since a NAP value would behave differently to a NULL for <> (and other operators), I wouldn't need to change my query.
- tabtab 7y agoRe: I'd have to remember to go over my query to add the ~ operator. I'd almost always use it no matter what. In fact if starting SQL over via a DeLorean, I'd reverse it to require "~" to make it null-sensitive. It's analogous to case-sensitive comparing. The vast majority of the time you don't want case-sensitive comparisons such that case-insensitive should be the DEFAULT, and you only add extra specifiers/functions if and when you need to do case-based comparing. If not, then you either end up with SQL full of "toUpperCase(x)", or problems keep popping up if somebody typed stuff wrong or the UI coders messed up. Similarly, if your SQL is full of NVL() or DENULL() or whatnot, it means the language was poorly factored for your domain, or even most domains. It flunked D.R.Y. in design, such as comparisons having the wrong default behavior.
- magicalhippo 7y agoYeah that'd be interesting.
- scottlamb 7y ago> Maybe we should have introduced NEX (non-existing) and NAP (non-applicable) as possible values in addition to NULL? Codd (the inventor of relational algebra) actually suggested this. I think the primary source is a book that may not be on the web. There's some discussion here (mostly saying why they think it didn't happen and wouldn't work out): https://arxiv.org/html/1606.00740v1/ https://arxiv.org/html/1606.00740v1/