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

Given:

The following Select statement:

select开发者_Python百科 case NULL
       when NULL then 0
       else 1
       end

Problem:

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

---

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.

---

I was going to add this as a comment to Aaron's answer, but it was getting too long, so I'll add it as another (part of the) answer.

The CASE statement actually has two distinct modes, simple and searched.

From BOL:

The CASE expression has two formats:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.

• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

When the simple CASE (your example) does what it describes as comparison it does an equality comparison - i.e. =

This is clarified in the later documentation:

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

• Allows only an equality check.

Because anything = NULL is always false in ANSI SQL (and if you didn't know this, you need to read up on NULLs in SQL more generally, particularly also with the behavior in the other searched comparison - WHERE x IN (a, b, c)), you cannot use NULL in a simple case and have it ever be compared to a value, with a NULL either in the initial expression or in the list of expressions to be compared against.

If you want to check for NULL, you will have to use an IF/ELSE construct or the searched CASE with a full expression.

I agree that it's kind of unfortunate there is no version which supports an IS comparison to make it easier to write:

select case colname
   when IS NULL then 0
   else 1
end

Which would make writing certain long CASE statements easier:

select case colname
   when IS NULL then ''
   when 1 then 'a'
   when 2 then 'b'
   when 3 then 'c'
   when 4 then 'd'
   else 'z'
end

But that's just wishful thinking...

An option is to use ISNULL or COALESCE:

select case COALESCE(colname, 999999) -- 999999 is some value never used
   when 999999 then ''
   when 1 then 'a'
   when 2 then 'b'
   when 3 then 'c'
   when 4 then 'd'
   else 'z'
end

But it isn't always a great option.

---

In addition to the other answers, you need to change the syntax for CASE slightly to do this:

SELECT CASE 
    WHEN NULL IS NULL THEN 0
    ELSE 1
    END;

Using the value in your syntax implicitly uses an equals comparison. NULL is unknown, and so is NULL = NULL, so with your current code you will always get zero 1 (geez I did it too).

To get the behavior you want, you can use SET ANSI_NULLS ON; however note that this can change other code in ways you may not be able to predict, and the setting is deprecated - so it will stop working at all in a future version of SQL Server (see this SQL Server 2008 doc).

---

You need to use the IS NULL operator. Standard comparison operators do not work with NULL.

---

Check out these MSDN articles about Null that may be useful:

• IS [NOT] NULL (Transact-SQL)
• Null Values