2

I have seen the graph below on a Stack Overflow post

enter image description here

However, I'm confused about the result of "p OR q", "p AND q", where "p" equal to "false", "q" equal to "unknown".

In the graph, the result of "p OR q" is "unknown", where "p" equal to "false", "q" equal to "unknown". But shouldn't the result be "false"?

Also, in the graph, the result of "p AND q" is "false", where "p" equal to "false", "q" equal to "unknown". But shouldn't the result be "unknown"?

Could someone tell me if I'm correct or wrong?

halfer
  • 19,824
  • 17
  • 99
  • 186
Thor
  • 9,638
  • 15
  • 62
  • 137

1 Answers1

4

The value NULL has a special semantic meaning in SQL. NULL means "unknown," and therefore any logical operation which depends on the value being known is unknown.

In the case of FALSE OR NULL, the result is unknown, because the outcome depends on what that NULL value might actually be. If that NULL were TRUE, then the expression would be TRUE, but if it were FALSE, then the expression would be FALSE. Since it could be either TRUE or FALSE, we therefore cannot say what the value of this expression will be, and the result is also NULL.

However, in the case of FALSE AND NULL, we can say what the outcome will be. Here, since FALSE and anything must be FALSE, the outcome will have to be FALSE, even if the value on the other side of AND be NULL.

Following on this, TRUE AND NULL has to be NULL, because depending on what that NULL might represent (i.e. TRUE or FALSE) the value of the overall expression would change.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you so so much for the clarification. Now everything makes perfect sense! 'NULL' has to be one of the most confusing part of sql. So glad to have experts like you to help out! THanks again! – Thor Mar 14 '17 at 04:30
  • 1
    @TonyStark It's especially confusing if you're coming from a Java or .NET background, where `null` has a different behavior. – Tim Biegeleisen Mar 14 '17 at 04:30
  • @TimBiegeleisen Not know about Java, but in C# nullable bool (`bool?`) implement the same logic for `|` and `&` operations. – user4003407 Mar 14 '17 at 11:26