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?
7条答案
按热度按时间bpzcxfmw1#
Just to show there are many ways to do it.
4uqofj5v2#
There is one more option but I'm not sure whether it'll be more elegant.
7cwmlq893#
Try something like this:
bkhjykvo4#
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 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!
rpppsulh6#
one more option is:
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