# Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say:

``````WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL
``````
• this would be the equivalent of the COALESCEs of all the permutations to be equal - but there is no non-aggregating MIN/MAX function

An alternative for 3 columns is:

``````WHERE (
COALESCE(a, b, c) = COALESCE(b, c, a)
AND COALESCE(a, b, c) = COALESCE(c, a, b)
AND COALESCE(a, b, c) = COALESCE(b, a, c)
AND COALESCE(a, b, c) = COALESCE(a, c, b)
AND COALESCE(a, b, c) = COALESCE(c, b, a)
)
OR COALESCE(a, b, c) IS NULL
``````

i.e. "All non-NULL columns a, b, c must be equal or all columns may be null"

Obviously a PIVOT/UNPIVOT implementation could be done or a complex CASE statement, but I'm looking for something relatively simple.

In this case, the columns are all integers, so I guess there is probably a math trick I could figure out.

• So many good answers, I'll have to evaluate them before awarding the accept, sorry. Jan 29, 2013 at 15:08

The most compact syntax I can find is:

``````SELECT *
FROM @T AS t
WHERE EXISTS
(
SELECT ISNULL(ISNULL(a, b), c)
INTERSECT
SELECT ISNULL(ISNULL(b, c), a)
INTERSECT
SELECT ISNULL(ISNULL(c, a), b)
);
``````

Based on an idea from one of my old blog posts that describes how to use `INTERSECT` and `EXCEPT` to replace comparisons like `a <> b OR (a IS NULL AND b IS NULL)` with `NOT EXISTS (a INTERSECT b`).

You can do the aggregate over columns using `VALUES(a),(b),(c)` or if you are using SQL Server pre 2008 you can do `SELECT a UNION ALL SELECT b UNION ALL SELECT c`.

``````SELECT *
FROM @T
WHERE (SELECT MIN(x) FROM (VALUES(a),(b),(c)) AS T(x)) =
(SELECT MAX(x) FROM (VALUES(a),(b),(c)) AS T(x)) OR
COALESCE(a, b, c) IS NULL;
``````

Update:

Something that by the looks of it should be a bit faster.

``````SELECT T.*
FROM @T AS T
CROSS APPLY (
SELECT MIN(x), MAX(x)
FROM (VALUES(T.a),(T.b),(T.c)) AS X(x)
) AS X(MinValue, MaxValue)
WHERE X.MinValue = X.MaxValue OR
(X.MinValue IS NULL AND X.MaxValue IS NULL);
``````

``````SELECT *
FROM @T
WHERE (SELECT COUNT(DISTINCT x) FROM (VALUES(a),(b),(c)) AS T(x))
<= 1 ;
``````
``````SELECT COUNT(DISTINCT columnValue)
FROM(SELECT a AS columnValue
UNION ALL SELECT b
UNION ALL SELECT c
) AS ColumnValues
WHERE columnValue IS NOT NULL
``````

This is easy to extend to more than three columns.