START WITH and CONNECT BY in Oracle SQL

The start with .. connect by clause can be used to select data that has a hierarchical relationship
 (usually some sort of parent->child (boss->employee or thing->parts).

We'll see a simple example to understand this,

create table test_connect_by (
  parent     number,
  child      number,
  constraint uq_tcb unique (child)
);


insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);
Parent = 5
Child = 2,3

insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);
Parent = 15
Child = 10,5

insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);
Parent = 17
Child = 9,8

insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
Parent = 38
Child = 15,17

insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);
Parent = 26
Child = 1,12

insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
Parent = null
Child = 38,26


Query:

select lpad(' ',2*(level-1)) || to_char(child) s
  from test_connect_by
  start with parent is null
  connect by prior child = parent;

Output:


38
   15
      10
       5 
   17
     9
     8
26
   1
   12