I am trying to understand 'Connect by prior' clause in oracle. I have created below table :
create table EMP_MGR ( mgr VARCHAR2(10), emp VARCHAR2(10) )
Below is the data of table:
MGR EMP
null f
f a
f b
f c
b x
b y
Now, When I run the below query
select mgr,emp
from emp_mgr
connect by prior emp = mgr
start with mgr is null
I am getting below output:
MGR EMP
null f
f a
f b
b x
b y
f c
I understand this output completely.
but the problem is when I execute the same query without 'Start with' clause I am getting below output :
MGR EMP
f a
f b
b x
b y
f c
b x
b y
null f
f a
f b
b x
b y
f c
Please explain why the output is repeated. Why I am getting extra rows when I am not using start by clause.
As I understand if I am not specifying the start with clause oracle will start from the root which is same as 'start with null'
Please assist.
Thanks!!!
When you omit the start with clause, connect by prior creates a tree from
every parent node!
So there's a new hierarchy for every row with at least one child. This becomes more obvious when you use connect_by_root to return the top node for each tree created and/or sys_connect_by_path to show the tree:
create table EMP_MGR ( mgr VARCHAR2(10), emp VARCHAR2(10) );
insert into emp_mgr values (null, 'f');
insert into emp_mgr values ('f', 'a');
insert into emp_mgr values ('f', 'b');
insert into emp_mgr values ('f', 'c');
insert into emp_mgr values ('b', 'x');
insert into emp_mgr values ('b', 'y');
select connect_by_root mgr, level, mgr,emp, sys_connect_by_path (emp, ',')
from emp_mgr
connect by prior emp = mgr
order by 1 nulls first, 2;
CONNECT_BY_ROOTMGR LEVEL MGR EMP SYS_CONNECT_BY_PATH(EMP,',')
1 f ,f
2 f c ,f,c
2 f a ,f,a
2 f b ,f,b
3 b y ,f,b,y
3 b x ,f,b,x
b 1 b y ,y
b 1 b x ,x
f 1 f c ,c
f 1 f a ,a
f 1 f b ,b
f 2 b x ,b,x
f 2 b y ,b,y
So if you add another row with 'a' as the manager, this creates a new tree one deep:
insert into emp_mgr values ('a', 'z');
select connect_by_root mgr, level, mgr,emp, sys_connect_by_path (emp, ',')
from emp_mgr
connect by prior emp = mgr
order by 1 nulls first, 2;
CONNECT_BY_ROOTMGR LEVEL MGR EMP SYS_CONNECT_BY_PATH(EMP,',')
1 f ,f
2 f b ,f,b
2 f c ,f,c
2 f a ,f,a
3 b y ,f,b,y
3 b x ,f,b,x
3 a z ,f,a,z
a 1 a z ,z <==========
b 1 b y ,y
b 1 b x ,x
f 1 f b ,b
f 1 f c ,c
f 1 f a ,a
f 2 b x ,b,x
f 2 a z ,a,z
f 2 b y ,b,y