That false friend also trips up programmers using floating-point for the first time, where NaN fills a similar role to NULL here.

I think CASE is very inconsistent to three-value logic, because it behaves like a usual IF/ELSE statement, lumping a Boolean NULL together with FALSE under ELSE. Consequent language design should have CASE WHEN .. ELSE ... UNDECIDABLE (the UNDECIDABLE is like a CATCH if any condition evaluates to null)

@Falco, that's only if you see ELSE as the equivalent of all previous tests having evaluated to FALSE. That thinking is true in other languages, but not SQL where the remaining possibilities (when things are not TRUE) are FALSE or NULL. The proper behaviour of NULL in most cases is not to "catch" it but to propagate it, and if you omit the ELSE limb altogether then the CASE expression will return NULL for any case not covered by the explicit tests.

@Steve but the missing syntax for NULL-results makes it very hard to write certain conditions. I think it is very inconsistent to NULL being infectious in most other constructs. Because it is hard to rebuild CASE to a three way logic - on the other hand you would just need NVL(...,FALSE) to make a true three-way-logic CASE behave like the current CASE.

I would even argue a NULL-consistent CASE statement should evaluate to NULL as soon as it encounters a single condition evaluating to NULL. Because it cannot decide if this condition is undecidable, making all other conditions moot.

So the claim in the question that 1 > null is false is false?

@CarstenS, the result of any operation involving any operand being NULL, is usually NULL, in accordance with the null propagation principle. So 1 > NULL is NULL, because one of the supplied operands is NULL. And because trilean logic has three values, there is no implication that something which is not true, must be false. The only implication of something being not true, is that it must be false or null (note I'm using the word "not" in the natural language sense here, not in the sense of referring to the behaviour of the NOT operator).

"It's a timeless complaint that SQL is too complicated, but the true situation is that there is a preponderance of naive programmers who want things to be simpler than necessary." Would you mind if I turn this into a 2x3 meters poster and hang it in my office?

The problem isn't that SQL is complicated, it's that it's implicit and obscure about the complications. The sane thing would be to have a dedicated way of dealing with nullability here, as modern languages generally support with a Maybe / Option type. That would make the expression a bit more verbose, arguably more complicated, but in a way that usefully guides the programmer to where the complicated stuff happens. E.g. case liftA2 (>) a b of { Just True -> a; _ -> b }

@leftaroundabout, it's true that SQL is not the best teaching language for SQL (irony noted), and both the syntax and the concepts are clearly not a fresh design. But there nothing implicit about the trilean logic; the concept of NULL is SQL's hallmark feature!

@Falco, I wouldn't treat NULL as "undecidable" - in my view, that isn't what the value means in SQL. When handling tabular data, as often as not it means "irrelevant", not "undecidable". Everyone can always think of improvements that suit particular cases, but not which better suit all usual cases.

+1 for a clear explanation of what I dislike but it doesn't really answer the question. "That ELSE in the original one-liner is doing a hell of a lot of work." Perhaps, but it's not at all helpful. I'd much rather it fail so that such logic errors are found sooner than a decade after they were introduced.

@Falco "I would even argue a NULL-consistent CASE statement should evaluate to NULL as soon as it encounters a single condition evaluating to NULL." That's a good point. I would prefer that as well. It would at least be more consistent.

@Falco I updated the question based on your observation. Please let me know if it that's not what you meant.

@Steve "the concept of NULL is SQL's hallmark feature!" Right, and I've recall any experience where I felt that was helpful. It's always a source of problems, in my experience.

@JimmyJames, see the edit I've added. The general principle is to be exhaustive with cases and avoid repeating sub-expressions. As for NULL always being the source of your problems, don't you ever use an outer join? Outer relational operators (and much else besides) would be impossible without the existence of NULL.

@Steve It's not always the cause if issues but it often is. And I'm not saying that NULLs are the issue. It's the three-valued logic that is the issue. It's readily apparent that we don't require three-value logic to support nulls. If A is 1 and B is null, there's a straightforward answer to A == B: false. There's also a straightforward answer to A != B: true. I've written a lot of code under these semantics and I can't think of a single time I ever pined for 3-value logic. I've also written a lot of SQL where I absolutely would have preferred 2-valued logic.

Your last alternative is interesting. Not sure that works in Oracle but I suppose if I could transpose the columns into a sub query and do a MAX, that would solve the problem. But that sort of points to the question. Why is it OK for MAX to select the largest non-null result and MIN to select the smallest non-null? Why is the MEAN/AVG of a set of values not null if one of the values is null?

"why can't we dispense with NULLs entirely?" is a misinterpretation of the question. The question is what we lose if SQL used 'normal' boolean logic: e.g. null = null is true, not null = not null is true.

@JimmyJames, because experience decades ago showed that the way aggregate operators were usually desired to work, was to exclude NULLs. The current default behaviour of the aggregates is also the most flexible behaviour. It's possible to easily patch the nulls up into normal values if you want them to be factored into the aggregate somehow, and it's also possible to easily propagate nulls using an expression of standard aggregates (such as checking COUNTs, then setting NULL if the counts reveal nulls to be present). However, the reverse is not true if the aggregates propagate nulls by default.

@JimmyJames, because if NULL = NULL were true, the join operators would not work properly. And in the ANSI 86 syntax, the join conditions went in the where-clause (made possible because of the fact that an inner join is algebraically equivalent to a cross join and a filter). It would be possible of course to define different equality operators with different behaviours, but then you'd get guys complaining that they'd fumbled the use of all these subtly different operators with different truth tables, and it wouldn't likely reduce the perceived complexity.

@Steve Right, so doesn't that kind of point to 3-valued logic as something that works in theory and not in practice? Is what is good for the goose not good for the gander?

"the join operators would not work properly" That's a bit of circular reasoning. They wouldn't work properly as they are defined now. They would work some other properly defined way.

So we are getting somewhere. Can you give an example of what problem that would cause? I've seen many bugs where people try to use 'where A = null'. Can you give some concrete example of the kind of problem you would expect?

Another example is 'AND (A <> B) or (B is null)'. I can't think of any examples where three-valued logic reduces the query complexity. Can you give one?

@JimmyJames, (assuming you're still on aggregates...) I think it's more that a foolish consistency is a hobgoblin of little minds. Nulls propagate through scalar operators because that is the usually desired behaviour. They don't propagate through aggregate operators because that isn't the usually desired behaviour. In each case, the default approach can be reversed with very limited additional coding. If the aggregates propagated by default, that however cannot be easily reversed. If the scalars didn't propagate by default, some solution would be needed for total ordering of the comparisons.

"because that is the usually desired behaviour" is it? That's what I'm looking for. When, why?

"some solution would be needed for total ordering of the comparisons" or an error is raised like when there's a division by zero.

@JimmyJames, on joins, it's important to remember that the relational operators are the fundamental ones - the crown jewels of the system. They work as they do because they have important algebraic properties, which different operators wouldn't have. For them to work, the equality operator must also work as it does. It is a tightly integrated design, where many things seem to have an interlocking dependency. What you get from that design is bang for the buck in terms of execution efficiency, high concurrency, and relative ease of programming and adaptation compared to what came before.

@JimmyJames, on aggregates, first the meaning of null is often "inapplicable", not "missing", and therefore it is natural to exclude inapplicable cases from aggregates. It's not natural to ignore inapplicable inputs for scalar operators. Second, with aggregates you're often in the realm of statistics where rough answers (ignoring missing) can be quite tolerable. Third, as I say, non-propagation of nulls is more flexible for aggregates, in that you can still achieve propagation with a one-liner. This isn't true for scalars - adding propagation where it doesn't exist tends to be long-winded.

"or an error is raised [on null] like when there's a division by zero" - that can't be the default behaviour of operators, otherwise your joins wouldn't generally work. And joins are the primary feature of the language.

I think I get what you are saying but you absolutely can do everything that SQL supports without using 3-valued logic. You note that 'joins generally wouldn't work' but most simple inner joins would be completely unaffected, no? That is, WHERE A = B when B is null works the same way for binary logic as 3-valued. Negated conditions would seems to be improve i.e.: no longer requiring additional conditions for nulls. What are specific concrete examples of the problems you would expect to see with using binary logic for queries?

@JimmyJames, your inner joins wouldn't work unless the join columns were constrained to not null. Moreover we're back to the start of my answer: most desired data storage and processing simply won't fit within these narrower constraints. So your fix for null, is just to cut the capability of the system, massively.

@Steve My reasoning for "undecidable" goes like this: In tabular data NULL usually means unknown, not existent or not applicable/irrelevant. For example every employee has a boss, except the CEO at the top, his boss = "NULL". Now if I ask for every employee if they are longer at the company then their boss - the the result is "not applicable" for the CEO.

Now if I have a new prospective employee whose salary is not decided yet, his salary may be "NULL" until the contract is signed. When selecting employees and grouping by salary band, the question in which salary band this employee falls is "undecidable" until we know how much he earns. He will fall into one definitive band once his contract is signed, but right now we don't know what that will be - data:NULL = unknown and CASE:NULL = undecidable.

@Falco, but the boss of the CEO isn't "unknown" or "undecidable", it's simply a non-existent relationship (which is fully known and decided). Therefore in the records the foreign key is null, and any calculations which relate to the boss will have one or more inputs as null and should have outputs as null. It was all done to death by Codd himself. Null in 3VL is used to represent missing, inapplicable, or whatever the designer wants - whichever it represents is not explicit in code. (1/2)

In practice, null is merely a facility (like the Integer type) which doesn't have a single consistent meaning throughout the world, or even in any one system. What it does have is a reasonably consistent behaviour, suitable for the usual purposes to which it is put. A 4VL version of the relational model, which distinguishes the missing and inapplicable cases systematically and explicitly, was found to be far too complicated to wrangle - it had no advantages in usability or simplicity. (2/2)

@Steve yes but asking questions about the non-existent boss or salary is undecidable. "boss.age > 20" cannot be answered with TRUE or FALSE, it's NULL - no answer possible, because the question doesn't make sense. Other languages would throw a null-pointer exception if boss is null. This is why I think a CASE should early-exit and evaluate to NULL if a condition returns NULL. Because it is neither TRUE nor FALSE but not decidable/not applicable.

@Steve " your inner joins wouldn't work unless the join columns were constrained to not null." I don't follow. In typically 2-valued logic, If A.x is 1 and B.x is null, A == B is false. What doesn't work about that for inner joins? If I want all the rows from B where B.x is 1, I don't need 3-valued logic to make that work.

@JimmyJames, but you seem to be assuming still the existence of the null value, just not it's special treatment by the operators. In which case, what is the result of applying the NOT operator to the value NULL? In 3VL the result is NULL, but what of your 2VL? Where does NULL fit in the series of integers, so that the range comparison operators work? Again, in 3VL a range comparison with NULL results in NULL. What of your 2VL? If the answer is to crash, then we just have a crashy 3VL, where the special null value leads in most cases to crashes instead of null results.

@Steve In 2-VL, if I have a condition like if 2 != null the result is true. This is very easy to understand. In fact, many, if not all developers I've worked with (initially) expect SQL to work this way. Your task is to show me e.g.: why having a special is null syntax is a better solution than the straightforward 2-VL. Failing fast is always preferred in my experience. I would much rather find bugs in development than in production. It's much better invalid operations to error than to produce some incorrect non-error result.

"Where does NULL fit in the series of integers" It doesn't. You either error in such case or the definition of your operator/aggregate/scalar to say that nulls are ignored. Nothing about 2-VL precludes this.

@JimmyJames, the reason why null mustn't equal null, is due to the way the join operators work. And the way the join operators work is what is necessary for them to acquire their alegbraic properties which are valuable for optimisation. Your proposal wouldn't "fail fast" - it would fail extremely slowly in practice. It's difficult to get a handle on exactly what you propose as an alternative. 2VL traditionally means Boolean logic, two values of true and false - yet you say the null value, the third value in 3VL, will still exist in your plan.

"it would fail extremely slowly in practice" Please explain why. Just repeating 'algebraic properties' isn't an explanation. I've also worrked extensively with solutions that don't use 3VL that were faster than DB alternatives, so I need a little more than an assertion. If you have a reference, that's fine. "exactly what you propose as an alternative" I've repeated this a number of times, but I'll take another crack at it: it would work similarly to how division by zero is handled. Execution of the statement would halt and an error would be raised.

@Steve Frankly this focus on the alternative seems rather silly. We know that other solutions exist and can solve these problems. It doesn't make sense for you to require me to iterate though all the possible alternative solutions for you to show a single benefit of 3VL.

@JimmyJames, I have limited space in these comments to keep explaining things that could justify a book (and would certainly justify another proper round of Q&A), but I suggest your alternative would fail slowly because the replacement of null outputs with crashes, does not alter the timeframe over which bugs become manifest, but the likely form of your alternative would be somehow less efficient to execute. The only cases in which I've encountered a division by zero error in database work, is where the crash was inconvenient, and the fix was WHEN dsor = 0 THEN 0 ELSE dend/dsor END!

@Steve "The only cases in which I've encountered a division by zero error in database work, is where the crash was inconvenient" Crashes are almost always inconvenient. What's more inconvenient is when a system produces incorrect results in production. In some fields, maybe getting correct results isn't that important, but it tends to be the most important thing in my decades of experience.

"I suggest your alternative would fail slowly because the replacement of null outputs with crashes, does not alter the timeframe over which bugs become manifest" Objectively wrong in my experience.

@JimmyJames, the division by zero crash was only inconvenient because it crashed instead of defaulting to either zero or null. Either would have been acceptable and correct results - the incorrect result was to crash. My error was simply forgetting to explicitly special case the zero divisor to avoid the crash - a special case that wouldn't have been necessary if it had resulted in null by default instead of a crash. As for replacing nulls with crashes, it's perfectly possible (with only modest effort) to write code that does intentionally crash on null. But null is not usually an error.

@Steve Right and when it crashed, you realized your error and fixed it. That's a reliable and repeatable solution to that kind of issue. Producing some wrong result because a meaningless question will often result in production defects. To wit, the specific problem I found has been in production for more than a decade. If the null comparison failed, it would never have made it past the developer's workstation. The nulls have always been in play.

@JimmyJames, the point you're missing is that if the division operator had been designed to return zero or null in that case - if it had been designed not to crash, by returning either of the values which could be a sensible default and alternative to crashing - then the program would have been already correct and free of error.

@Steve Purely by chance in one specific scenario. There are many scenarios where that would not be the right result. It's actually a well-known problem that floating-point NaN results cause many bugs, for example.

@JimmyJames, yes. My argument here is that, by throwing on null, more spurious exceptions would be thrown in cases where there is no real error. It was erroneous of me to have written code that threw, but it wasn't erroneous of me to have expected a value to be produced. My error was in not circumventing the throwing behaviour and forcing all cases to produce a value, and no case to throw. I regularly encounter null in its completely legitimate capacity as a missing value or inapplicable field which exists by design, not just as an unthrown exception and symbol of error. (1/2)

The problem with NaN is that it isn't systematic in any language. I don't know the rules for it myself offhand, except that it's specific to float values and float operators. (2/2)

@Steve "My argument here is that, by throwing on null, more spurious exceptions would be thrown in cases where there is no real error." I doubt it but perhaps. The thing is those errors won't go unnoticed. I've already pointed you to the concept of 'fail-fast' and I'm not going to reiterate what that article explains.

@Steve No, the specific problem with NaN is that instead of immediately failing when you divide by zero, you get this special value which has weird properties such as adding it to a 'real' value produces NaN. That's what trips people up. Sound familiar?

"The thing is those errors won't go unnoticed." - NULLs aren't errors! Hence the analogy drawn with a case where a zero divisor was not an error, but a legitimate special case. The problem with NaN is that it is too obscure for me to even bother knowing. I don't struggle to master the logic. I'm not raving about its devil-sent absurdity. I simply don't encounter NaN from one decade to the next, rather than NULL which is a general language feature of SQL which I see daily.

@JimmyJames, the problem is in your definition of "valid" and "invalid". All operations on null are valid operations in SQL. I understand your argument that null is sometimes being used in a capacity where it shouldn't be regarded as valid to process the null value. But there are solutions to deem null values strictly as errors - such as the not-null type constraint. The example you gave in your question did not produce a null from an invalid comparison - it propagated a null that was already present on a nullable column and which was a valid operand to a comparison operator.

' the problem is in your definition of "valid" and "invalid"' I'll grant that I am using it informally in a context where it has a formal meaning. That is sloppy on my part. I think you get my meaning, though. Trying to compare (< or >) against a null is a not a sensible thing to do. It's not that the rules aren't consistent and predictable (they are) it just that it adds a lot of implicit behavior that (in my experience) many people don't expect and it becomes very hard to understand exactly what a complex query does and whether it is correct (i.e.: does what it was intended to do)

@JimmyJames, I think the contradiction is between valid operations being applied to invalid purposes. That is, the error is in the programmer choosing to apply certain valid operations when he shouldn't. But I don't agree that the scope of valid application of operators with null-propagating behaviours, is so small (or unimportant to the whole) that they should be struck out of the language. Range comparison operators can appear in join control expressions, and here their current behaviour for null input is mandatory. Everything in SQL which seems complicated, has it's justification.