Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
So many good answers, I'll have to evaluate them before awarding the accept, sorry. –  Cade Roux Jan 29 '13 at 15:08

4 Answers 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.

share|improve this answer

A variation on @Mikael's answer:

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

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

share|improve this answer

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);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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