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

``````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
``````

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.

Assuming you want all `NULL`s 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`

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
``````