SQL Server 2005 - Using Null Compared

Question

This is more a question satisfying my curiosity. Given the following statement:

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

Why am I returning "1" instead of "0"

Change it to:

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

Correctly returns "0"

I know that NULL comparisons can be tricky, but this specific example slipped through our code verification process.

Any clarification would be greatly appreciated.

Answer 1

This is due to 3-digit logic . In the first case, Unknown does not evaluate to true, so you end up in else

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

In the second case, you are executing 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 NULL comparison operators. Whenever you want to compare a value with a value that can be NULL, you need to use the IS statement to explicitly check if the value can be NULL. Or you can disable the ANSI_NULLS parameter 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 that:

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

Literature:

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

Answer 3

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

For the values ​​to be evaluated as you expect, run the script with SET ANSI_NULLS OFF in front.

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