T-SQL - Comparing two variables values with possible NULL

Question

I am trying to write a T-SQL statement that will compare two nullable variables (either int or varchar), @x and @y, and return FALSE if both values are equal or both values are equal to NULL, and return TRUE otherwise. A and B are both placeholders for possible values.

+------+------+--------+
| @x   | @y   | Result |
+------+------+--------+
| NULL | NULL | False  |
| A    | A    | False  |
| B    | A    | True   |
| A    | B    | True   |
| NULL | A    | True   |
| A    | NULL | True   |
+------+------+--------+

I can use COALESCE(@x,@y) to verify that both @x and @y are NULL. Since no value can ever be equal to (or not equal to) NULL, I cannot use <> or = operators. What would be the most compact way to write such statement? Do I have to state a bunch of conditions, such as: (@x is not NULL and @y is not null and @x <> @y) or (@x is not null and @y is null) or (@x is null and @y is not null)?

Answer 1

How about the following:

DECLARE @x varchar(10) = 'b',
        @y varchar(10) = 'a';


SELECT CASE WHEN COALESCE(@x,'') = COALESCE(@y,'') THEN 'FALSE'
            ELSE 'True'
       END AS Test

Note that you may want to choose some random value for COALESCE(@x,'')... Something that you'd never find in your data.

Answer 2

What about this? FALSE when...

ISNULL(@x,'') = ISNULL(@y,'')

Answer 3

You can use Coalesce to check for two NULL values and = to check for equal non-NULL values: not( Coalesce( @x, @y ) is NULL or @x = @y ).

Note that the following code does not introduce a magic value, e.g. by using Coalesce( A, '' ), that must not occur in the data.

As Dale K. suggested, applying a function to a column in a where clause generally renders the condition un-sargable.

declare @Samples as Table ( X VarChar(1), Y VarChar(1) );
insert into @Samples ( X, Y ) values
  ( NULL, NULL ), ( 'A', 'A' ), ( 'B', 'A' ), ( 'A', 'B' ), ( NULL, 'A' ), ( 'A', NULL );

select X, Y,
  -- Note: The result values are flipped rather than including   not   in the condition.
  case when Coalesce( X, Y ) is NULL or X = Y then 'False' else 'True' end as Result
  from @Samples;

Demo: db<>fiddle.