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?

Solution 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;

Solution 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.

Solution 3

I always use ISNULL function. I think ISNULL function saves you from writing longer scripts.

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

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

Comments

[Arif YILMAZ]

why dont you check if @A is null ?

[Sateesh Pagolu]

I explained why his code didn't work. my answer is more specific to his input data.

[xanatos]

You are using a value inside the domain of possible values in the ISNULL(). @A = 1, @B = NULL returns SELECT 1, while @A = 0, @B = NULL returns SELECT 3.