# 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.