combinations (not permutations) from cross join in sql
If I have a table that I'd like to cross join to itself, how can I remove the duplicate rows? Or to put it another way, how can I do a "order doesn't matter" cross join?
So for example, if I have a table T:
field |
-------
A |
B |
C |
and I cross join to itself so that i don't get the A | A rows
T as t1
cross join
T as t2
on t1.field != t2.field
I would get the following:
field | field
------+-------
A | B
A | C
B | A
B | C
C | A
C | B
However, to me A, B is the same as B, A.
Is there a good way to remove these duplicates? In other words, I want the combinations not the permutations.
T as t1
inner join
T as t2
on t1.field < t2.field
FWIW, you can just use INNER JOIN
for this, it's not strictly a CROSS JOIN
. MySQL (and perhaps some other RDBMS) treats these two types of join as identical, but in ANSI SQL, a cross join has no join condition -- it's a deliberate Cartesian product.