Sql Server: CASE Statement does unexpected behavior when comparing to NULL

Question

Given:

The following Select statement:

select case NULL
       when NULL then 0
       else 1
       end

Problem:

I'm expecting this to return 0 but instead it returns 1. What gives?

Answer 1

Generally speaking, NULL is not something you should attempt to compare for equality, which is what a case statement does. You can use "Is NULL" to test for it. There is no expectation that NULL != NULL or that NULL = NULL. It's an indeterminate, undefined value, not a hard constant.

-- To encompass questions in the comments --

If you need to retrieve a value when you may encounter a NULL column, try this instead:

Case
  When SomeColumn IS NULL
  Then 0
  Else 1
End

I believe that should work. As far as your original post is concerned:

Select Case NULL
  When NULL then 0 // Checks for NULL = NULL
  else 1 // NULL = NULL is not true (technically, undefined), else happens
end

The trouble is that your Case select automatically attempts to use equality operations. That simply doesn't work with NULL.