Search code examples
sqloraclecartesian

oracle sql creating combinations same table without repeat

How I can solve this? I need to eliminate the duplicated of the Cartesian product, of a table with itself. I thought of using "connect by". Thanks.

create table foo (
num number(2)
);

insert into foo values (1);
insert into foo values (2);
insert into foo values (3);

select a.num,b.num
from foo a, foo b;

NUM NUM
--- ---
 1   1 
 1   2 
 1   3 
 2   1 * duplicated
 2   2 
 2   3 
 3   1 * duplicated
 3   2 * duplicated
 3   3
Solution

  • You can try this:

    select a.num,b.num
  from foo a cross join foo b
 where a.num <= b.num