7

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.

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

4 Answers 4

8

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

0
5

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

A variation on @Mikael's answer:

SELECT * 
FROM @T
WHERE (SELECT COUNT(DISTINCT x) FROM (VALUES(a),(b),(c)) AS T(x))
      <= 1 ; 
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.