25

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.

EoghanM
  • 25,161
  • 23
  • 90
  • 123
Ramy
  • 20,541
  • 41
  • 103
  • 153

1 Answers1

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Sorry to respond to this after several months, but I am just confirming: this will create from, say, a list (A, B, C), combinations, as opposed to permutations: ((A,B), (A,C), (B,C)), right? Also, if I wanted to make the combinations from a subset of the list as defined by a value from some other column, I would just add a WHERE clause, right? Thanks. – Brash Equilibrium May 25 '12 at 13:41
  • 1
    @BrashEquilibrium, in combinations, order is not significant; (A,B) is the same subset as (B,A). This solution makes sure only one of these is chosen. While it means t1.field will always be ordered before t2.field, that doesn't make it a permutation. And yes, you can use a WHERE clause to restrict the matches further. – Bill Karwin May 25 '12 at 16:14