15

I was looking at an SQL query recently and found what I think is likely a bug. It was related to case statements on inequalities. I was trying to replace it with a min/max type alternate and when testing, I was getting different results. Here's a simplfied example of the SQL that I was trying to simplify:

CASE A > B THEN A ELSE B

The upshot was that in the case statement, if either value in the inequality was null, the else statement was the result. That is: 1 > null is equivalent in a CASE statement to false and null > 1 is also equivalent to false. So regardless of which one is null (or both,) the ELSE clause ends up as the result.

My initial assumption was that this could be replaced with a function such as a greatest or least (Oracle) but that's where I went wrong. If any argument to those functions is null, the result is null. That's opposed to the situation with the case statement where only in the case that B is null, will the result be null (i.e.: if A is null, the result will be whatever B is.)

OK. I figured out my major malfunction and probably identified a defect. Here's the question: Is there any practical value to this complexity? I can't think of any time that this kind of 3-valued logic has been useful, but I can think of innumerable times that it has been a cause of bugs. I know there is a theory underlying it. Is it simply non-pragmatic or is there something more to this idea that I am missing?

To clarify my 'complaint', throughout my education and most, of my coding career, I can expect certain things to be hold (using SQL syntax) such as:

  • if A <> B is false, then A = B is true.
  • if A <> C is false and B <> C is false then A <> B is false.
  • if A > B is false, then A <= B is true.
  • if A is null, and B is null, A = B is true. Or simply: A = A and NOT A = NOT A

None of these hold in SQL if nulls are involved.

The alternative to this isn't some novel solution. I would just prefer more standard boolean semantics. And my question is are there any fundamental reasons that SQL needs to violate those assumptions when nulls are involved. Would it make SQL harder to use in some way that I am not seeing? As for what should the result of 'A > B' be if one of them is 'null'? That's a good question. I suppose an error would be fine, or if there's some non-error result, it should work such that if A > B is false, and A != B, B > A is true.

I realized as I was looking through the answers and comments that part of my annoyance is the behavior of greatest and least in Oracle. If any of the values passed to these is null, the result is null. This means that greatest(1, 2, null) -> null and least(1, 2, null) -> null. Naively this implies that all the values are the same. But that's not what it means. It behaves like a silent exception. I realize that this is not a direct result of SQL 3-valued logic but it seems related to me. I would expect it to work like min or max works across a number of rows. In any event, the larger question still holds around how nulls are treated in SQL.

To make this more concrete, this is the structure of clause I was attempting to simplify:

CASE WHEN COALESCE(apple, pear) >  
  (CASE WHEN kumquat > orange THEN kumquat ELSE orange END)
  THEN COALESCE(apple, pear)
  ELSE (CASE WHEN kumquat > orange THEN kumquat ELSE orange END)

It's clear (for reasons that are outside the scope of this question) that the intention is to get the greatest value of the coalesced apple/pear, kumquat, and orange values. So I rewrote it as:

GREATEST(COALESCE(apple, pear), kumquat, orange)

Which I consider much clearer. However, when I tested the change, I was getting different results for the reasons already discussed. Note that while my replacement was flawed, the original has a subtle defect. That is if kumquat is null, we get whatever the value of orange. If orange is null, we get null.

I came up with this which is closer to the original intent and probably works based on some assumptions about the data:

GREATEST(COALESCE(apple, pear), COALESCE(kumquat, orange), COALESCE(orange, kumquat))

But I find this unsatisfyingly obfuscated. Side note: suggestions for a cleaner solution to this specific issue are welcome. Unfortunately, I cannot change the schema or the way the data is created.

P.S. In one of the comments @Falco made a good point about this coming from a different angle. In a nutshell, this is the behavior of CASE A > B THEN A ELSE B in SQL CASE statements (A on x-axis, B on y-axis):

   | 1 | 2 | ?
   --------------
1  | 2 | 2 | 1
2  | 2 | 2 | 2
?  | ? | ? | ?

Wouldn't this make more sense?

   | 1 | 2 | ?
   --------------
1  | 2 | 2 | ?
2  | 2 | 2 | ?
?  | ? | ? | ?

Sorry to keep updating this but I the answers and comments are helping me get to the underlying problem I have with this. Basically, while SQL supports 3-valued logic, there are many places where things are collapsed into binary conditions. And the way that happens is awkward and confusing. For example, a JOIN condition either succeeds or fails. There's no (to my knowledge) unknown result of a join condition.

condition | result
-------------------
true      | success
false     | fail
unknown   | fail

It's this 'impedance' mismatch or asymmetry that I think creates issues. And while the answers have been thought-providing, none so far have shown any example argument or example of how 3-valued logic makes things easier or otherwise better. The only thing so far has been this wiki page which states that this is way to address the semipredicate problem or in simplistic terms, nulls are an alternative to raising exceptions.

An acceptable answer will provide a specific concrete example of when 3 value logic simplifies or improves a solution over common 2-valued logic solutions. Informally, when is 3-valued logic something other than a footgun. Any example where 3-valued logic saved you or someone you know from a problem instead of creating one will suffice.

References:

1

9 Answers 9

5
+700

A minor frame challenge: your question does not seem to be "when is three-valued logic useful?" but rather "when is the specific flavor of three-valued logic that SQL uses useful?"

To illustrate the distinction, consider the "optional/maybe" suggestion in another answer: it is also a three-valued logic, but with different semantics. To wit, in most programming languages that have the concept, attempting to use a non-existent value in a logical proposition results in an immediate termination of the entire expression as an error, rather than propagating it.

As to when this specific set of semantics are useful? Occasions do arise, but often someone familiar with the semantics used by other languages are using a mental model that will not lead them to think in those terms. This isn't necessarily a good thing or a bad thing per se, although in general thinking in a model that does not align with the language actually being used leads (as you note) to a high chance for bugs.

I would argue that SQL's NULL value is best described in English as neither "a lack of a value" nor as quite "we don't know", although the latter is closer. Rather, I would characterize it as "undecidable" or, alternatively, what is sometimes referred to as "Mu" — meaning "your question has an invalid assumption".

  • The question in an outer join is "for each element in A, what elements in B match up to it?" with the presumption that there are in fact any that do — so when none do, that is the answer.
  • For most logical and comparative operators, there is no meaningful answer when at least one of the operands is unavailable. "Is 1 greater than some value that is not available?" is impossible to answer… but so is "is a value that is unavailable equal to another value that is unavailable?"

The largest "gotcha" involved is that "if/then/else" is still treated as a "true binary" construct, in that unlike an outer join, there is no implicit assumption that the control operand has a value (if there were, then the correct answer would be NULL because the entire proposition would be undecidable). Note that this isn't an inconsistent definition or semantic, it is just the most obvious case where this happens, and the fact that it happens in one of the most used logical constructs is arguably a poor design decision.

To answer the literal question: yes, such cases exist. As given in another answer, an example would be "if age >= 18" being used to control availability of some content. If you actually mean "are there cases that cannot be replicated using the logic I am more familiar with" (where a missing value that isn't accounted for simply blows up), then no — either set of semantics can fairly easily (if somewhat verbosely) be made to behave like the other. It is, however, more concise for asking some sorts of questions in the domain that SQL is related to (set calculus).

Addendum to address comments:

I think both the part asking for a specific example and the comment about "if age >= 18" actually tie together, in that is one example where you can deliberately make use of the fact that it will fail both when age is < 18 and when age is unknown. That doesn't invalidate "I would prefer Option" — as originally noted, the two sets of semantics can be converted using exactly that sort of tool. Keep in mind that the expression is not in fact "downmixed" to false — it evaluates to NULL, and because the "if" conditional is treated as a strict statement, both NULL and false fail it.

It may be helpful to keep in mind that SQL, as a language, is fundamentally structured around manipulating and querying data sets — as such, some of the semantics of its operations are actually derived from set theory based boolean algebra, rather than arithmetic boolean algebra (the type most programmers are used to). They are derived just as consistently, but from a different starting point, in this case one that is more "natural" to the domain which the language was actually written to deal with.

As a somewhat skewed analogy, consider verb handling in German and English. In German, "like Yoda you speak; your verbs at the end of the clauses you put." Rendered in English, this is still entirely comprehensible, but it is going to sound "odd" and there are some cases where such an "unfiltered" translation of a German sentence would introduce ambiguity in the English form that would not be present in the original. Neither one is "more correct", but if you try to speak in one while thinking in the other, you're very likely to get results that may be workable but also don't necessarily do quite what was intended.

At the end of the day, the not-necessarily-entirely-satisfying answer is "SQL was not written for folks who develop primarily linear code". It is its own beast, in the same way (and more or less at the same level) as procedural, functional, and object-oriented code are their own beasts. This is one fairly major reason for the popularity of using tools like ORM frameworks to adapt between OO code and SQL — it helps avoid the inevitable complexities that folks lose track of when they don't use a language as a primary tool on a regular basis. They do have a cost, but that cost is often more than worth paying if your primary audience are developers who don't sling SQL for a living.

Edit 2:

One thing I just caught, re-reading the original question, was the statement "if A<>B is false, then A=B is true". This highlights exactly the point that things go off the rails: what you are expecting to be true… actually is. Because when A and/or B is null, "A<>B" and "A=B" both evaluate to NULL, not true or false. Only when that is "collapsed" by a forced binary decision (for example, use in a 'where' clause, where there is no 'third option' when deciding whether a row is included) does it get treated as "effectively false".

25
  • You are hitting the mark here. Specifically "As to when this specific set of semantics are useful? Occasions do arise, but often someone familiar with the semantics used by other languages are using a mental model that will not lead them to think in those terms." This is concise way of stating my quandary. Am I failing to use these constructs correctly because of my mental model? What are some examples of the 'occasions that arise'?
    – JimmyJames
    Jul 21, 2023 at 20:10
  • I do want to challenge your challenge. The fact is that Option approach is still a 2-valued logic solution. Your truth table for (e.g.) AND has 4 scenarios, not 9. The Option option is (IMO) more evidence that we don't need 3-valued logic at all. Exceptions on the other hand ...
    – JimmyJames
    Jul 21, 2023 at 20:27
  • Why did someone downvote the best (most relevant) answer so far?
    – JimmyJames
    Jul 21, 2023 at 20:30
  • 1
    "manage all relationships on the language side and use the DB as pure dumb storage": I think that if you are there, a non-relational or 'NoSQL' DB is probably the way to go. SQL works great (to a point.) Success is rarely purely a result of chance. Where I see things go wrong is when people go deep with SQL. I specifically use the word 'deep' here because that's where these null semantics start creating havoc, IMO.
    – JimmyJames
    Jul 25, 2023 at 22:05
  • 1
    This answer was really the only one that addressed my question. While it didn't specifically list out ways that 3VL as implemented in SQL actually solves practical problems, it was very useful in helping me get much more precise in my thinking around this. Thanks.
    – JimmyJames
    Jul 28, 2023 at 20:42
21

Is there any practical value to this complexity?

You can already avoid NULLs by making every storage column NOT NULL and using only INNER JOINs for processing.

The problem is that most data simply doesn't fit well into those constraints, and that's the practical value of those constraints not being compulsory.

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.

With CASE A > B THEN A ELSE B, the error is in thinking that the values of A and B fall into a range of values with a total ordering.

This is true in SQL when A and B are non-nullable (and it's unconditionally true in many other programming languages), but it isn't true when one or both are nullable.

In this respect, the > operator is what linguists call a false friend, because most people first learn its meaning in the context of another programming language where the comparison operators have certain properties and certain relationships to one another (such as that <= fully covers what > doesn't).

In SQL, the result of any comparison operator is a trilean value: TRUE, FALSE, or NULL. This is different from most languages where the result of such an operator is a boolean.

And the result of any operation involving a NULL value as an operand, is usually NULL (though there are various exceptions). This behaviour is called null propagation.

Written out in full and covering the cases exhaustively, CASE A > B THEN A ELSE B is a shorthand for:

CASE
    WHEN A > B THEN A
    WHEN A <= B THEN B
    WHEN A IS NULL THEN B
    WHEN B IS NULL THEN B
    END

That ELSE in the original one-liner is doing a hell of a lot of work.

The original programmer who introduced the bug, probably had a mental model of the exhaustive cases like this:

CASE
    WHEN A > B THEN A
    WHEN A <= B THEN B
    (no other cases possible)
    END

...and forgot to consider at all what the correct behaviour should have been when either of the operands were NULL.

Evidently the operands can be NULL, either because the storage columns can accept NULL (probably by design and by necessity), and/or because an OUTER JOIN was employed (again, probably by design and by necessity).

It's that part about there being some necessity of using NULLs, when the database engine is nevertheless capable of excluding their use, which shows that the bug which the original programmer introduced doesn't arise from a lack of facility (or extravagant complexity) in SQL, but from an error in managing the essential complexity of the problem he faced. He simply fumbled it.

EDIT:

Based on the following edited into the question:

CASE WHEN COALESCE(apple, pear) > (CASE WHEN kumquat > orange THEN kumquat ELSE orange END)
  THEN COALESCE(apple, pear)
  ELSE (CASE WHEN kumquat > orange THEN kumquat ELSE orange END

I would restructure this into a series of steps with exhaustive coverage of cases (boilerplate omitted for clarity):

apple_or_pear_or_null = CASE
    WHEN apple IS NULL AND pear IS NULL THEN NULL
    WHEN apple IS NOT NULL THEN apple
    WHEN pear IS NOT NULL THEN pear
    END

kumquat_or_orange_or_null = CASE 
    WHEN kumquat IS NULL AND orange IS NULL THEN NULL
    WHEN kumquat IS NOT NULL AND orange IS NULL THEN NULL
    WHEN orange IS NOT NULL AND kumquat IS NULL THEN orange
    WHEN kumquat > orange THEN kumquat
    WHEN orange <= kumquat THEN orange
    END

result = CASE
    WHEN apple_or_pear_or_null IS NULL THEN kumquat_or_orange_or_null
    WHEN kumquat_or_orange_or_null IS NULL THEN NULL
    WHEN apple_or_pear_or_null > kumquat_or_orange_or_null THEN apple_or_pear_or_null
    WHEN apple_or_pear_or_null <= kumquat_or_orange_or_null THEN kumquat_or_orange_or_null
    END

(Excuse any errors in translation here, this is off the cuff on a mobile.)

What you can see here is that the number of cases is in fact quite complicated. It's far from clear that the intention is to get "the greatest value" of all four.

If it was simply the greatest sought from all 4 possibilities, you'd do something like:

SELECT MAX(value) FROM (VALUES (apple), (pear), (kumquat), (orange)) AS tbl(value)

...which will (typically) return the greatest of the available values, or NULL if no value is available.

(Note: I'm accustomed to SQL Server, I'm attempting illustration, not to give a verbatim Oracle solution.)

EDIT 2:

Having reinterpreted the question once more as "why can't we dispense with NULLs entirely?", the bottom line is that relational algebra requires a special default value as part of the workings of most join operators.

The use of relational algebra (in the broadest definition of the concept) is desirable because of the algebraic properties of its operators which allows the execution engine enormous latitude for automatic, ongoing, whole-system optimisation of database workloads.

It's possible to avoid NULLs by using a special value within the domain of the base type, or with an out-of-band flag in an adjacent field, but the consequence of this ends up involving just as much complexity as NULL but with greater bespoke variety. The general-purpose NULL value is already the systematic solution to these (older) alternatives.

1
17
+400

This is not a bug. It's a feature. Having no information is different from having a neutral value.

NULL is not 0. NULL means there is no value known:

  • If you expect for a column that NULL would be like 0, you should make this column NOT NULL and initialize its value by default to 0.
  • The absence of information can be important in a number of cases. Imagine an online service where some age check is needed. If age is NULL, age is not known; it's not 0. And you may have either to require the value to be completed, or make some decision based on a business rule (e.g. if age not known, disable services requiring an age check).

In your specific case, if you want to consider NULL as zero only in a specific formula, you could use the SQL COALESCE() function. All mainstream RDBMS support it. But first, you need to make sure that one or two of the values being NULL is not yet another case that requires a different action:

CASE COALESCE(A,0) > COALESCE(B,0) THEN COALESCE(A,0) ELSE COALESCE(B,0)  

More on NULL value and its semantics and tips and tricks here.

EDIT

In the meanwhile your question was enriched multiple times, including with the COALESCE proposal (giving the wrong impression that my answer paraphrases the question, which was not the case when I wrote it). So let me add some considerations:

  • Codd introduced NULL in relational algebra to represent either an unknown value or the fact that a value is not applicable (see this article for precise references)
  • Later, Codd even proposed a four value logic, for distinguishing between NULL+applicable and NULL+not/applicable (see resaearch paper NULL in databases revisited. This distinction was not taken over in SQL - but is implemented in Excel ;-)
  • A binary comparison operator requires two operands. In 3vL, if one is missing, the operation is evaluated to NULL (in the sense not applicable), because there is only one operand (or less) and it the ioeration is not defined with one operand or less (and since the condition is not true, else is fired). All this is perfectly consistent with Codd's initial definition of NULL.
  • Moreover, without NULL, there would be no outer join possible. In the outer join, the relevant columns for corresponding missing rows are NULL to ean that having a value is not applicable. And if you would any comparison with such columns would not be applicable either.
19
  • 4
    Small point: there are often slightly confused statements about whether NULL is a value of a special kind, or a "missing" value (i.e. a non-value). My preferred view is that NULL is a value that is treated specially by operators, and the rationale for that special treatment is because the NULL value is intended generally to represent missing or inapplicable information in a systematic way.
    – Steve
    Jul 21, 2023 at 8:01
  • 2
    More subtlety: in some domains it may happen that "no value, not applicable, none" and "we don't know" are different. But NULL doesn't help to distinguish that. Only "no value in the row/col", without further details.
    – Pablo H
    Jul 21, 2023 at 15:32
  • 2
    "seems to combine the two point of views" - that's exactly the problem. In my view, NULL is a value because it is a defined state that can be processed into other defined states. It may be a defined state that represents that something is missing, but the record that something is missing isn't missing, it is present - (it's a "known unknown", in Rumsfeldian terms). I'm unclear what advantages exist for the other way of thinking - but I suspect I employ a level of indirection between what the records say, and what they mean or represent, whereas the other way conflates the two.
    – Steve
    Jul 21, 2023 at 15:35
  • 3
    @Christophe Yeah, I'm not trying to change SQL. I have enough Sisyphean goals. I'm just wondering if I'm missing something. Am I trying to row upstream in my battles with nulls? Is there a different way to think about it that would streamline our SQL and make it easier to reason about?
    – JimmyJames
    Jul 21, 2023 at 16:36
  • 1
    I think age check is a great example: One wants an explicit "WHEN age IS NULL" check to trigger special behavior, like asking the user to verify their age. Which is different from any value the age might have. - But I also think SQL could have benefited from a second non-value like UNDEFINED in JavaScript, which behaves the same, but is distinct. But that would need even more discipline for data administration, to make sure the right value is used (and reality shows, NULL is already too complex for most users)
    – Falco
    Jul 24, 2023 at 8:51
3

Does 3-valued logic ever provide practical benefits over 2-valued logic?

I can give you a concrete example where using 3VL was very useful (Whether it's a good example is up for debate.)

A program I worked on had a requirement to pass along certain data sets to an older legacy system. This system wasn't really designed for this kind of operation so sending superfluous data would get its knickers in a twist just as badly as sending the incorrect data. The peculiarities of which dataset to send when turned out to be a huge source of bugs, endless streams of if statements and vast amounts of duplication. It was also impossible to make changes to without doing a full release of the code base (This was in a bank in the "good old days" so a release cycle could be months long)

Someone cleverer than I decided to use a truth table / matrix of columns to represent the 20-ish decisions required to determine if a dataset should be required. It looked kind of like this:

ProductId IsABusiness decision-2 ...decision-N Dataset
1 1 1 1 ds1
2 0 1 0 ds2
1 0 1 1 ds3
1 1 0 0 ds2

and so on.

Pretty standard so far, but still a little cumbersome for this use case, because some datasets would apply for any ProductId (as an example) or not care about whether the application was for a business.

This is where NULL comes in. A NULL in a column meant that we did not care about that column in our decision-making process: a NULL in the productId column meant the dataset applied to all products, a NULL in the IsABusiness column meant that the rule applied regardless of whether the applicant was a business or not.

We could express some fairly interesting decision using this style (e.g., a Business applying for a specific type of loan which already has a loan with the bank and is older than 3 years), without having to expressly write out NumDecisions^2^NumProducts rows. If a new requirement came in, adding a column didn't break existing rules.

Does this count as a practical benefit of 3VL over 2VL? I think so.

1
  • 1
    Thanks for this. I really appreciate the straight answer. Can you elaborate or clarify how three-valued-logic tied into the solution. Maybe I need more coffee but I'm not seeing where rules like 1 > null -> null fit into this.
    – JimmyJames
    Jul 27, 2023 at 13:54
2

You need to fight this guy : https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql

Sure you could throw an error when you hit a NULL or a NaN and some operations do.

The benefit of not doing so is that when you are running your operation on sets of data rather than a single operation, having a single row cause the whole set to fail is not seen as a good thing.

Given the preponderance of null values in databases if you chose to throw an error when you hit a null in a calculation everyone's sql would be littered with these kind of "how do i solve the divide by zero problem" IFNULLs

You would still get silent failures because you would have wrapped your greater than calculation in the various coalesce functions to make it work. Plus your SQL would look ugly

28
  • 1
    I don't see any reason to fight anyone on that answer. It's an SO question about how to solve a programming problem. I am not asking a programming problem. I am asking about (roughly) language design. Imagine you were migrating a system and you have a choice: a 'NoSQL' with a boolean query syntax database and an SQL DB that uses trinary syntax. All else equal (and it never is, but humor me) what are the practical 'pros' of three-valued logic for a team whose members tend to have low to moderate programming skills?
    – JimmyJames
    Jul 24, 2023 at 20:43
  • Sort of an aside but something I realized in this (painful) attempt to get an straight answer is that it wouldn't be too hard for an SQL query to provide static guidance on this. That is, if a column allows null, the engine could refuse to compile your statement if you haven't declared what you want to do with null values. Along the same lines, it seems to me that the CASE statement should have a 'NULL' clause along with ELSE. If you choose 3-valued logic, I think you should commit to it. No pun intended.
    – JimmyJames
    Jul 24, 2023 at 20:56
  • 1
    my point is that SQL does have your two valued logic for division. Where it does so people complain that it isn't three valued logic and add functions to make it three valued.
    – Ewan
    Jul 24, 2023 at 20:57
  • 1
    LOL. I tried. I really tried: destroyallsoftware.com/talks/wat
    – JimmyJames
    Jul 24, 2023 at 21:20
  • 2
    In passing: getting people to avoid building highly complex queries is almost always the right answer. Most of the "true power" of SQL is primarily useful for reporting purposes where the query is innately complex to express a complex question… but rarely is that very useful from a programmer's point of view unless they are writing reporting functionality. And reporting is easier to write when other queries aren't doing strange deep magic to accomplish their work. Jul 25, 2023 at 21:59
1

I have been involved with databases for nearly 20 years of my now retired career.

The need for null aware logic has surfaced many times, often when we did not expect it. Say you have a table of users that can apply for various low-income benefits. Suddenly the business requirement for a new benefit is invented that requires the applicant to be over 18 years of age. So we start collecting date of birth, but existing users don't have this data. Using nulls means that standard reporting functions like average age of birth return correct data - they work against records that have the data and ignore nulls

So given this background embrace null logic, and carefully evaluate case logic in particular.

3
  • Are you claiming that you can't solve such problems with 2-valued logic? I'm certain that you can.
    – JimmyJames
    Jul 24, 2023 at 15:01
  • 1
    @JimmyJames Of course you can roll your own. But SQL has solved the problem so why reinvent the wheel?
    – kiwiron
    Jul 25, 2023 at 6:02
  • No idea what you talking about. I never mentioned 'rolling my own'. I don't think you understand the question.
    – JimmyJames
    Jul 25, 2023 at 14:19
1

Does 3-valued logic ever provide practical benefits over 2-valued logic?

Yes. It's frequently the case in programming that values don't exist, and that using some valid value as a default is awkward or unacceptable. In short, it's often very useful to be able to distinguish between false and unknown.

Consider, for example, a database that tracks donations to a political campaign. You might design a table so that it has a has-donated column with a nullable boolean value: you either know that a potential donor has donated to the campaign, or you know that they haven't donated, or you don't yet know whether they have or haven't. In order to express the same thing with two-valued logic, you'd need multiple columns for has-donated and has-not-donated, because has-donated being false might mean that the potential donor has definitely not donated, or it might mean that you don't know. And if you did have those two columns, you'd have to write a bunch of code to manage them carefully, so that you never ended up in a weird situation where both has-donated and has-not-donated are true.

SQL has a NOT NULL modifier that prevents rows from being added unless they contain values for any columns marked NOT NULL. This simplifies code because you know that you don't have to consider NULL values in columns so marked, and that's a big benefit in itself.

The motivation SQL's three-valued logic is similar to that behind optional types in languages like Swift, Kotlin, Rust, etc. Optional types let the programmer explicitly state her intentions about whether a variable is allowed to have "no value", and they let the compiler ensure that code that uses optional variables checks for a valid value before using it. They eliminate an entire class of programming errors where programmers forget to check for sentinel values like nil.

If SQL's three-valued logic seems onerous, it might be that you haven't considered the benefits of using NOT NULL in cases where that makes sense.

12
  • 1
    A fair attempt at addressing the question but I'm not asking what null means or whether nulls are OK (it's a valid question, just not what I am asking here.) I'm asking what 3VL around nulls provides. You can have 2VL with nulls. For example, if the condition null <> 1 resulted in true, instead of null, that would be one valid way to implement 2VL while allowing nulls.
    – JimmyJames
    Jul 26, 2023 at 20:53
  • 1
    @JimmyJames There are a number of different 3-valued logics (e.g. HT, Lukasiewicz, Kleene, etc). If the logic considers 3 values such as true, false, and null, then it's some sort of 3VL, not 2VL. And again, the value of SQL's particular 3VL is that it lets you handle nulls in a graceful way (instead of, say, throwing an error) while still not assuming that the value is something other than null.
    – Caleb
    Jul 26, 2023 at 21:25
  • 2
    @JimmyJames All the current answers here correctly explain the issue from different perspectives. I added mine mainly because I think that the comparison to optionals in other languages is compelling and, if you're familiar with optionals, explanatory. It's clear from the question that you would prefer different behavior (I would just prefer more standard boolean semantics), and I think that your wish for something else is making it hard for you to see any benefit to the current behavior. As such, I'm not sure that any good-faith answer will satisfy you.
    – Caleb
    Jul 26, 2023 at 21:54
  • 1
    @JimmyJames, do you have some sort of chip on your shoulder about the idea that people can make inferences - not assumptions - about your agenda and strength of feeling on a topic? Quite apart from the fact that you've explicitly admitted - somewhere amongst the thickets now - that your agenda at work is to reduce the use of SQL. This answer lists a "benefit" of 3VL - which you claim to be seeking - in the paragraph beginning "Consider, for example...". And for clarity, Caleb has already commented that he does not think the use of null is distinct from using 3VL (which I also agree with).
    – Steve
    Jul 28, 2023 at 17:45
  • 1
    @JimmyJames, nobody's discussing your feelings, we're perceiving your apparent agenda and tailoring our remarks so as to be pertinent to that. That doesn't mean the answers are at cross purposes, or that information has been withheld. There's been considerable engagement, and there's been no limitation on you asking for more. But you don't seem to integrate anything that is actually said. You say you understand all about 3VL. But you don't seem to get that 3VL is so called because null forms the third logical value. You posit a "2VL with null", without distinguishing this from 3VL. (1/2)
    – Steve
    Jul 28, 2023 at 19:52
1

Regarding the COALESCE-issue in your edits: The problem here is not really due to three-valued logic specifically. The problem is you have a set of columns that you want to filter and sort. Unfortunately, SQL only supports filtering and sorting on sets of rows, not on sets of columns. If the data was rows in a single column, it would be trivial to exclude NULL's and then take the MAX, if you want the highest non-null value.


3-valued logic is necessary to be able to query datasets that contain unknown values, i.e. NULL's. Without 3-valued logic, such a query would either fail or give incorrect results in some cases. Neither is desirable.

NULL in SQL mean unknown or not applicable. (This should not be confused with the semantics of null's in programming languages like Java or JavaScript (or None in Python) where it means no object or just nothing. Nothing is very different from unknown! For example, a person having no address is very different from a person having an unknown address.)

You are specifically questioning the semantics of greatest. If any value in the list provided is NULL, the result is NULL. This is derived from the behavior of binary comparisons - if one of the operands is unknown, the result is unknown.

This might be annoying in this particular case where you just want the largest non-null value. But the general principle of three-valued logic is extremely important because it prevent the database from giving misleading answers to queries.

Lets say you have a database of customers. Some of them have registered their age, but not all (i.e. age is NULL). Now for some legal reason you need to check if there are any customers below the age of 21, so you ask for the age of the youngest customer. If this query just ignored NULL's it could give you a dangerously wrong answer. E.g. you might begin selling alchold to customers below the age of 21, which might land you in jail.

More generally the principle is that a query should not lie if it doesn't know the answer. If the query is x < 21 and x is unknown at the time, you want the query to tell you the answer is unknown. Answering either true or false would potentially be incorrect and could lead to serious trouble.

The query engine could signal an error and abort the query in case of an operation involving nulls. But this would also prevent the database from giving a correct and useful output in many cases. Sometimes, knowing that an answer is unknown is quite useful information!

For example, you might want a list of all customers below the age of 21 - acknowledging that you don't know the age of all customers. But you can't use age < 21 since if age is NULL for some customers, the whole query will be rejected. Couldn't you just filter the NULL-valued away in a WHERE-condition? No, because a relational query engine is allowed to re-arrange the order of operations, so you can't guarantee the nulls are filtered away before the comparison.

Even worse, you might have a query that works correctly because the current data doesn't happen to have any null values even though the column is defined as nullable. Adding a valid record that contains a NULL value could then cause cascading failures in the system.

The only safe approach would then be to always add a case-expression guarding against NULL, eg.

  SELECT 
    CASE WHEN age IS NULL 
            THEN NULL
            ELSE age >= 21
    END AS is_of_drinking_age;

This will work, but it is a lot of boilerplate which just introduces your own 3-value logic in an ad-hoc manner. It might be more explicit but it is certainly not more practical.

All of this trouble is of course because we allow NULL values in data. But even if we disallow NULL's in base tables, they may still appear in left/right/outer joins, so they are not easily eliminated.

21
  • 1
    But it's not as if there's no solution for this in 2-valued logic. I'm not sure what you mean by 'necessary' here.
    – JimmyJames
    Jul 26, 2023 at 17:21
  • "If SQL didn't have three-valued logic it would be forced to return either true or false". It could raise an error like many (most?) SQL engines do for division by zero.
    – JimmyJames
    Jul 26, 2023 at 17:27
  • @JimmyJames: Yes it could raise an error on operations involving a null value. But that would in many cases prevent queries from giving a correct result even when it would be possible to give a correct result. This would make the query engine overall less useful. I have amended the answer a bit to detail the problems.
    – JacquesB
    Jul 27, 2023 at 12:01
  • "many cases prevent queries from giving a correct result even when it would be possible to give a correct result" By not erroring on such comparisons, it allows queries to produce incorrect results without warning. That is far more common in my experience than the 'it worked even though I wrote the wrong code' scenario. When something raises an error, you will find out. When something fails silently, those issues can go unnoticed and really bad things can happen in the real world.
    – JimmyJames
    Jul 27, 2023 at 14:05
  • Let me try to come at it from a different direction. Are you saying that this is the benefit is convenience? That the reason this useful is because people writing SQL are not forced to consider how missing/non-applicable results should be treated? A kind of 'get what you get' situation?
    – JimmyJames
    Jul 27, 2023 at 14:09
0

Key Point

Think of SQL as a way of processing lots of data in parallel, and NULL means that this value is unknown. Greatest(....) should return NULL if one or more data values are NULL because one of the unknown values might be the greatest, or they might not be... We do not know.

Detail

This is an interesting question because the correct answer flies in the face of conventional programming wisdom.

In creating application code, the perceived wisdom is that you should write your function to always succeed with the result. If an error occurs, it should be raised as an exception. The purpose of this is to simplify your codebase. The alternative is checking every function return value to verify the call succeeded. Very soon, the amount of error handling overwhelms your code base.

In SQL, at a simplistic level, it can be "just data storage and retrieval". However, as products and services grow, things never remain simple long enough.

As application requirements change, additional values are needed for data entities. For some data, inserting a default value where the actual value is unknown may be possible. In the world of Data protection, subject access requests and laws requiring data records to be accurate, it is not acceptable to insert a valid value into the actual data leaving no way to know if that was the default or an actual data value. This is why NULL is essential as a valid data value.

As a programmer, I'd love it if all business logic occurred in the code layers and SQL databases were restricted to CRUD operations. Performance-wise, it is many times more performant to move the code to the data than to ship the data to the code. In other words, application and business logic inevitably occur within the SQL Database server environment.

In the NoSQL world, there is everyday awareness of the pattern Map, Transform, Reduce, where each phase happens separately and allows the map and transform stages to be highly distributed, with only the reduce step tending to happen sequentially.

With SQL, Select, Join, and Where statements are part of the map phase. Computing additional row values from existing data is part of the transform phase, and functions such as Group By and Count are part of the reduce phase. And this is where the simplicity ends.

The value of SQL is its ability to run complex business and application logic efficiently by executing the code close to the data source. This brings with it the challenge of handling situations where logic cannot provide the answer, and this is the problem that NULL solves.

How to handle errors when execution of the program occurs over multiple sets of data in parallel.

Your SQL application may be processing 20,000 data records through the business logic. Rather than failing the whole process, isn't it better to provide as much of the output as possible and use NULL to identify where the data could not be obtained?

Thinking about the use of logic and SQL functions this way means that having functions passed NULL as one of their data values probably should return NULL as their result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.