Returning rows where all non-NULL values are equal

Question

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.

Answer 1

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).

Answer 2

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);

Answer 3

A variation on @Mikael's answer:

SELECT * 
FROM @T
WHERE (SELECT COUNT(DISTINCT x) FROM (VALUES(a),(b),(c)) AS T(x))
      <= 1 ; 

Answer 4

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.