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

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

May 13, 2025 - 17:06

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: