Dealing with Nulls in Comparison Tests in SQL Server 2005

I was writing a Stored procedure today, and I ran into a problem where if one of the values is null (whether it be from a SELECT statement or whether it be a passed in parameter) my expression would evaluate to false where it ought to evaluate to true.

SET ANSI_NULLS ON;
DECLARE @1 INT;
DECLARE @2 INT;
DECLARE @3 INT;
DECLARE @4 INT;

SET @1 = 1;
SET @2 = NULL;
SET @3 = 3;
SET @4 = 3;

IF ((@1 <> @2) OR (@3 <> @4))
   BEGIN
     SELECT 1;
   END
ELSE
   BEGIN
     SELECT 2;
   END
SELECT @1, @2, @3, @4

Returns:

2

1, NULL, 3, 3

I expected it to return:

1

1, NULL, 3, 3

I know I'm missing something simple, anybody have any idea what it is?

Related Question

SQL: Why are NULL Values filtered Out within this WHERE clause?

From stackoverflow

• One way of dealing with this is you can wrap your NULL values in a known unexpected value, i.e. -1:

  IF ((ISNULL(@1, -1) <> ISNULL(@2, -1)) OR (ISNULL(@3, -1) <> ISNULL(@4, -1)))
     BEGIN
       SELECT 1;
     END
  ELSE
     BEGIN
       SELECT 2;
     END
  

  Obviously if -1 was a possibility then use a different number. If there is no non-possible value, then you will have to use CASE statements.

  A concise way of assigning NULL values to a 'safe' value follows:

  SET @1 = ISNULL(@1, -1)

  This allows the contional test code to remain 'clutter-free'.

  ck : @Gortok: Yes, SET @1 = ISNULL(@1, -1)

  George Stocker : I edited your answer to include your comment, and marked as accepted. Thank you.

• Any comparison that involves a NULL will evaluate to NULL instead of True or False. Hence the ELSE block of your code gets executed. Because although Null is not the same as False, it definitely isn't the same as True.

  George Stocker : Yea, I surmised as much. I'm hoping for a cleaner solution if you have one.

  codeulike : Use IsNull() with a dummy value as the others are suggesting. Or expand your logic with '@1 is null' type checks.

  codeulike : Like BradC's answer.

  codeulike : Or you can SET @1 = IsNULL(@1,-1) in the lines before if you want a tidier version.

  codeulike : wow, we are all totally scrambling to tell you the same thing!

• Yes, nulls are a pain. A few ways to handle them. Some of these are MS-SQL specific functions:

  Method 1: Be real explicit about all the options

  IF ((@1 <> @2) 
  OR (@1 is NULL AND @1 IS NOT NULL)
  OR (@1 is NOT NULL AND @1 IS NULL)
  OR (@3 <> @4)
  OR (@3 is NULL AND @4 IS NOT NULL)
  OR (@3 is NOT NULL AND @4 IS NULL))
  

  Method 2: Us a function to change the null into something else, that you know won't match:

  IF ((IsNull(@1,-1) <> IsNull(@2,-1) 
  OR (IsNull(@3,-1) <> IsNull(@4,-1))
  

  Sometimes this first option is better because it is more explicit. The second one has the side-effect of matching a NULL to a NULL.

  Edit: if you want to set them ahead of time, just do

  SET @1 = IsNull(@1,-1);
  

  If it is null, it will set it to -1. If not, it will leave it alone. I think its cleaner to do it INSIDE the function, per my Method 2.

  George Stocker : Yea, the first truth test you listed didn't seem like a viable option. I'm looking for a concise way to re-assign the parameters to -1 if they end up being null (as the result of a select statement or parameters passed in). Any ideas there?

  BradC : Yes, that's what IsNull() does!

  dotjoe : set @1 = isnull(@1, -1);

• Here's a way to do it without functions, and also might be easier to read. (My eyes start to cross when I see a bunch of NULL-testing functions close together.)

  IF (@1 IS NULL AND @2 IS NULL) OR (@1 = @2)
  BEGIN
      IF (@3 IS NULL AND @4 IS NULL) OR (@3 = @4) 
      BEGIN
          SELECT 2
      END
      ELSE
      BEGIN
          SELECT 1
      END
  END
  ELSE
  BEGIN
      SELECT 1
  END
  

• If you want to be even more confused, try running the opposite test, as follows:

  SET ANSI_NULLS ON;
  DECLARE @1 INT;
  DECLARE @2 INT;
  DECLARE @3 INT;
  DECLARE @4 INT;
  SET @1 = 1;
  SET @2 = NULL;
  SET @3 = 3;
  SET @4 = 3;
  IF ((@1 = @2) AND (@3 = @4))
     BEGIN     SELECT 1;
     ENDELSE   
     BEGIN     SELECT 2;   
     END
  SELECT @1, @2, @3, @4
  

  The result will not be what you expect from your previous experiment.

  The reason is that SQL engages in a complicated three value logic when it's evaluating expressions that might contain NULLs.

  NULL = 3 is not TRUE. It's not FALSE either. It's NULL. NULL = NULL is not TRUE. It's not FALSE either. It's NULL.

  It's easy to get all mixed up with this. The best way to avoid confusion is to constrain all your critical data to be NOT NULL. However, the requirments may prevent you from doing this, in which case you are just going to have to figure out 3 valued logic, SQL style.

  Don't ask me to defend this.

  (In some environments, UNKNOWN is used in place of NULL for results that are not TRUE or FALSE.)