Geek Answers Handbook

Comparing two variables in SQL

Let's say you have the following 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, the variable @A is "1", and the variable @B , of course, does not match. I execute this piece of code in Microsoft SQL Server Management Studio 2014, and I get a result of "3". This means that @A does not match @B , but also does not differ from @B . How is this even possible? What am I missing here?

+4
source share
4 answers

You cannot compare null with other values. You need to handle zeros separately. So it 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;
+7
source

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;

since NULL comparisons should always be handled separately in SQL.

I inverted the cases != And = because tsql does not have the logical XOR operator, because I want to consider NULL to be NULL.

Note that then SELECT 3 will no longer be.

+2
source

I always use the ISNULL function. I think the 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;
+1
source

You can easily compare variables with INTERSECT , since it is NULL sensitive:

 DECLARE @A BIT = NULL ,@B BIT = 1; IF EXISTS ( SELECT @A INTERSECT SELECT @B ) SELECT 'equal'; ELSE SELECT 'not equal';

In addition, when you need to do such comparisons in complex queries, this can improve performance because it allows you to use indexes.

0
source

More articles:


All Articles