Comparing two variables in SQL

Question

Lets say you have the next code:

DECLARE @A INT = 1,
        @B INT = NULL;

IF (@B != @A)
    SELECT 1;
ELSE IF (@B = @A)
    SELECT 2;
ELSE
    SELECT 3;

As you can see variable @A equals '1' for sure and variable @B certainly doesn't. I'm executing that part of code in Microsoft SQL Server Management Studio 2014 and I'm getting '3' as result. That means @A is not the same as @B, but it's also not different than @B. How is that even possible? What am I missing here?

Answer 1

You cannot compare null with other values. You need to handle nulls separately. So,this will work

DECLARE @A INT = 1,
        @B INT = NULL;

IF (@B != @A or @B is  null )
    SELECT 1;
ELSE IF (@B = @A)
    SELECT 2;
ELSE
    SELECT 3;

Answer 2

The correct version should be:

IF (@B = @A OR (@B IS NULL AND @A IS NULL))
    SELECT 2;
ELSE IF (@B != @A OR @B IS NULL OR @A IS NULL)
    SELECT 1;
ELSE
    SELECT 3;

because NULL comparison must always be handled separately in SQL.

I inverted the != and the = cases because tsql doesn't have a logical XOR operator, because I want to consider NULL equal to NULL.

Note that then the SELECT 3 won't ever happen now.