SQL Server Comparing two NULL-able variables in T-SQL

5ktev3wc  于 2023-02-28  发布在  其他
关注(0)|答案(7)|浏览(127)

Let's say I have two variables which may be NULL, and I want to check if they are different.

But, I want to:

  • Treat two NULLs as equal (not as NULL).
  • Treat NULL and non-NULL as unequal (not as NULL).

I know I could just write:

DECLARE @v1 int = ...;
DECLARE @v2 int = ...;

IF (
    (@v1 IS NULL AND @v2 IS NOT NULL)
    OR (@v1 IS NOT NULL AND @v2 IS NULL)
    OR @v1 <> @v2
)
    PRINT 'Different!';

But is there a more elegant way?

bpzcxfmw

bpzcxfmw1#

Just to show there are many ways to do it.

IF EXISTS(SELECT @v1 EXCEPT SELECT @v2)
    PRINT 'Different'
4uqofj5v

4uqofj5v2#

There is one more option but I'm not sure whether it'll be more elegant.

IF NOT(NULLIF(@v1, @v2) IS NULL AND NULLIF(@v2, @v1) IS NULL)
PRINT 'Different!';
7cwmlq89

7cwmlq893#

Try something like this:

DECLARE @v1 INT = ...;
DECLARE @v2 INT = ...;

IF ISNULL(@v1, -1) <> ISNULL(@v2, -1)
    PRINT 'Different!'
bkhjykvo

bkhjykvo4#

DECLARE @v1 INT = NULL
DECLARE @v2 INT = 1

IF CHECKSUM(@v1) != CHECKSUM(@v2)
    PRINT 'Different!'
chhqkbe1

chhqkbe15#

Without knowing anything about the wider scenario in which this code will be used, we often use a null-equivalent value, which is a value which is valid for the datatype, but outside the program's valid range. For example, if your program is only using positive integers, using a null-equivalent value of -1 would mean that the following is possible:

IF ISNULL(@v1, -1) != ISNULL(@v2, -1) -- or COALESCE if you prefer
    PRINT 'Different!';

If the full range of integer values are valid entries, then it's not possible to use a null-equivalent value. If that's the case, there is absolutely nothing wrong with your original method.

I would advise anyone against getting too clever with things like this, it will just cause you pain later on. Keep it simple!

rpppsulh

rpppsulh6#

one more option is:

SET ANSI_NULLS OFF
IF @v1 != @v2 
    PRINT 'Different!';
SET ANSI_NULLS ON
goucqfw6

goucqfw67#

For those who waited this long... SQL Server 2022 has finally implemented IS [NOT] DISTINCT FROM operator.

https://learn.microsoft.com/en-us/sql/t-sql/queries/is-distinct-from-transact-sql

相关问题