Matching 3 columns in row - if one column is null then exclude it from comparison

Question

A   B   C    Equal
1   1   1    Y
2   2   NULL N

Hi, I have 3 columns which I need to compare : Columns A,B,C. If these 3 columns are the same then column 'Equal' = 'Y'

But, if a column is null, then it must be excluded from the comparison, e.g. in the example Row 2 has a null, but the remaining columns are all the same, so the Equal should be yes - because all non-null values are equal

http://www.sqlfiddle.com/#!3/dedd9/2

I thought I could use isnull(C,A) = A but it does not work. Could anyone help please

 declare @t table
 (
 A decimal,B decimal, C decimal
 )

 insert Into @t(A,B,C) values (1,1,1)
 insert Into @t(A,B) values (2,2)

 select A,B,C,'Equal' = case when ISNULL(A,B) = B and ISNULL(B,C) = C and ISNULL(C,A) = A then 'Y' else '' end
 from @t

score 3 · Answer 1 · answered Jul 04 '14 at 08:53

Assuming you want all NULLs to count as equal, I think this will work. But I haven't thoroughly tested it, or even thought about it for very long...

 select A,B,C,'Equal' = 
    case when COALESCE(A,B,C,0) = COALESCE(B,C,A,0)
          and COALESCE(B,C,A,0) = COALESCE(C,A,B,0)
     then 'Y' else '' end
 from @t

COALESCE will pick the first non-NULL value from its arguments, so when testing A against B and then B against C, in each case we look 'forward' for a non-NULL value, with a fallback for when they're all NULL

score 2 · Accepted Answer · answered Jul 08 '14 at 11:48

Another way, that can be easily extended to more than 3 columns:

SELECT a, b, c,
       Equal = ( SELECT r = CASE WHEN MIN(v) = MAX(v) THEN 'Y'  -- all non-null values equal
                                 WHEN MIN(v) < MAX(v) THEN 'N'  -- at least 2 non equal 
                                 ELSE 'Y'                       -- all nulls
                            END 
                 FROM (VALUES (a), (b), (c)) AS x (v)
               ) 
FROM t ;

Tested at SQL-Fiddle.

score 1 · Answer 3 · answered Jul 08 '14 at 10:45

You were almost there with your query. However, your query fails, when validating isnull(A,B) = B, when B is null, since NULL does not equal NULL in sql.

try this:

 declare @t table
 (
 A decimal,B decimal, C decimal
 )

 insert Into @t(A,B,C) values (1,1,1)
 insert Into @t(A,B) values (2,2)
 insert Into @t(A,C) values (2,2)
 insert Into @t(A,C) values (2,3)
 insert Into @t(A,B,C) values (2,2,3)


select 
  A
, B
, C
, 'Equal' = case when ISNULL(A,B) = ISNULL(B, A)
                  and ISNULL(B,C) = ISNULL(C, B)
                  and ISNULL(C,A) = ISNULL(A, C)
            then 'Y' else '' end
 from @t

Matching 3 columns in row - if one column is null then exclude it from comparison

3 Answers3