SQL Server 2005 - Using Null in a comparison

Question

This is more of a question to satisfy my own curiosity. Given the following statement:

DECLARE @result BIT
SET @result = CASE WHEN NULL <> 4 THEN 0
                   ELSE 1
              END
PRINT @result

Why do i get back "1" instead of "0"

Changing it to:

DECLARE @result BIT
SET @result = CASE WHEN NULL IS NULL
                        OR NULL <> 4 THEN 0
                   ELSE 1
              END
PRINT @result

Correctly gives me back "0"

I know NULL comparisons can be tricky, but this particular example slipped through our code review process.

Any clarifications would be greatly appreciated

Answer 1

This is because of 3 valued logic. In the first case Unknown does not evaluate to true so you end up in the else

DECLARE @result BIT
SET @result = CASE WHEN Unknown THEN 0
                   ELSE 1
              END
PRINT @result

In the second case you are doing True or Unknown which evaluates to true.

DECLARE @result BIT
SET @result = CASE WHEN True
                        OR Unknown THEN 0
                   ELSE 1
              END
PRINT @result

Answer 2

This is due to the ANSI SQL standard and the behavior of comparison operators with NULL. Whenever you want to compare a value with a value that could be NULL, you need to use the IS operator to explicitly check for the case where the value could be NULL. Or you can disable the ANSI_NULLS option in SQL Server.

The following examples do what you want:

DECLARE @result BIT  
DECLARE @input INT

SET @input = NULL
SET @result = CASE WHEN (@input IS NULL OR @input <> 4) THEN 0  
                   ELSE 1  
              END  
PRINT @result  

Or this:

SET ANSI_NULLS OFF

DECLARE @result BIT 
SET @result = CASE WHEN NULL <> 4 THEN 0 
                   ELSE 1 
              END 
PRINT @result 

References:

http://msdn.microsoft.com/en-us/library/ms188048.aspx

Answer 3

From a purely technical perspective, the reason why the first statement returns a 1 is because ANSI_NULLS is set to "ON" which treats NULL as "Unknown" which follows the ISO standard.

To have the values evaluate as you're expecting, run your script with SET ANSI_NULLS OFF ahead of it.

In real life, of course, ISNULL() is the most awesome/safest approach.