How can I write a "no two values in [column_list] are equal" condition?

Question

In SQL, the condition that a set of columns must have equal values is a bit repetitive:

SELECT * FROM t
WHERE col1 = col2
    AND col2 = col3
    AND col3 = col4;

But that's not half as bad as a "no two values can be equal" type condition:

SELECT * FROM t
WHERE col1 != col2
    AND col1 != col3 AND col2 != col3
    AND col1 != col4 AND col2 != col4 AND col3 != col4;

Can I rewrite this query without explicitly comparing each pair of columns, and if so, how?

In a perfect world I wouldn't have a reason to do this in the first place; barring that, a solution that works for an indefinite number of columns in Oracle 11g would be nice. I have some flexibility with respect to DBMS and I'm open to (constructive) suggestions.

Answer 1

I agree that it is a weird situation. But in theory you can unpivot columns into rows (key-value pairs) and then group by the values, checking having count(*) > 1. It should not return any rows if all columns have unique values.

select min(column_name), max(column_name), count(*)
  from (select * from table  t
         unpivot (column_val FOR column_name IN (COL1, COL2, COL3, COL4))
       )
group by column_val
having count(*) > 1;

Unpivot clause you can build dynamically.