sql - Oracle - explanation of hierarchical query

Question

I have a question about hierarchical SQL.

sample data:

create table group_ (
group_id number);

create table set_(
set_id number);

insert into group_ values(1);
insert into group_ values(2);
insert into group_ values(3);
insert into set_ values(1);
insert into set_ values(2);

SQL:

SELECT  group_id,
        sys_connect_by_path(set_id, ', '), LEVEL
FROM    set_
CROSS   JOIN group_
CONNECT BY prior set_id < set_id
ORDER   BY group_id, combo nulls first,lvl;

My goal is to get all possible combinations of 2 tables, and it's working, but I've got repeated values so i have to use distinct/unique caluse. The question is - why they are appearing?

Answer 1

You're doing a UNION ALL. Effectively you're selecting everything from group_ and then selecting it again after joining with the set_ table.

Change to UNION and the duplicates will be removed.

See: http://sqlfiddle.com/#!4/7d079/3