Another fun thing with NULLs

I saw this on a post at SQLServerCentral and wanted to share.

The poster had a CASE statement and was wondering why it didn’t work as expected. Essentially they were doing something like:

CASE WHEN @a = @b OR (@a IS NULL AND @b IS NULL) THEN 1 ELSE 0

CASE WHEN NOT(@a = @b OR (@a IS NULL AND @b IS NULL)) THEN 1 ELSE 0

And they wanted to know why both were returning 0 when @a or @b were set to NULL. The issue here is that any normal predicate involving NULL returns an unknown. They had tried to compensate with the OR, which got them the result they wanted in the first statement, but didn’t understand why it did that.

To fully understand I want to break down what each bit returns here, assuming @a IS NULL and @b is some value

First, @a = @b returns UNKNOWN, represented in T-SQL as NULL. This is because you are comparing unknown (@a) to an actual value (@b), and the SQL engine can’t say whether that is TRUE or FALSE.

Second, @a IS NULL returns TRUE, and @B IS NULL returns FALSE. Put together, @a IS NULL AND @b IS NULL returns FALSE as an AND requires both parts to be true to return TRUE. If you replace the AND with an OR it would return TRUE, as OR only requires one part to be true.

So, what we have here is UNKNOWN OR (TRUE AND FALSE). This simplifies to UNKNOWN OR FALSE when we solve the bracket. And, logically, that has to simplify to UNKNOWN. After all, what we are asking here is “are either this unknown result or this false result true?” The false result is obviously not true, but the unknown result might be.

This brings us to the essential issue in the original question, which is how an expression and the reverse of that expression could both return the same value. Taking the first CASE statement, that is saying when the expression is TRUE return 1, else return 0. The expression returns UNKNOWN, but the CASE statement can’t just say “I don’t know”, it has to return 1 or 0, and with the way predicates work in SQL Server it will only return 1 if the expression returns TRUE.

Taking the second CASE statement, that is saying when the reverse of the expression is TRUE return 1, else return 0. But the expression returns UNKNOWN, so what’s the reverse. It’s helpful here to think of UNKNOWN as more of a placeholder than a proper response. The logic will always behave as if there is a TRUE or FALSE response that would be returned if we had all of the facts. Therefore, what it wants to return is the reverse of that TRUE or FALSE, but it doesn’t know which is the correct one, so the NOT(expression) also returns UNKNOWN when the inner expression returns UNKNOWN. This obviously leads to the CASE statement returning 0 again, as it only returns 1 when the predicate returns TRUE.

An alternative way of thinking about it is to imagine someone asks you if my eyes are brown. Clearly there is an answer to that question, but you as a reader of this blog who’s never met me won’t know what it is. Now what if someone asks you if my eyes are not brown? Again, the answer is “I don’t know.” Now what if the question is “do any of these 10 people have brown eyes?” At this point we’re getting into the territory of IN/NOT IN predicates, which are another area where NULLs cause issues. If I’m one of those 10 people, then even if you know all of the other 9 don’t have brown eyes, you can’t give a definite yes or no to that question, and therefore can’t give a definite yes or no to the reverse either. Then, because in T-SQL (as in most coding languages) predicates only trigger on a definitive yes/TRUE, the predicate logic will behave as if the expression returned a FALSE result, but for the reasons outlined in this post it’s important to know that it actually returned UNKNOWN.