Root level node is not coming in result

I am new to the Oracle Hierarchical Queries. I have one table and have the below data.

Table Data Result Data My question is why parent id(100) is not included in the result? Below is the query.

select id, lpad(' ',4*(LEVEL - 1)) || CHILD CHILD, LEVEL
from temp
START WITH PARENT = 100
CONNECT BY  PARENT = PRIOR CHILD;

Regards, Bhushan

Answer

If you to include starting value in your output then use union all:

select 0 id, '100' child, 0 lvl from dual
union all
select id, lpad(' ', 4 * level ) || child, level 
  from temp
  start with parent = 100
  connect by  parent = prior child

or recursive CTE:

with c(id, child, lvl) as (
    select 0, '100', 0 from dual 
    union all
    select t.id, lpad(t.child, (c.lvl + 2) * 4, ' '), c.lvl + 1 
      from c join temp t on t.parent = c.child)
search depth first by id set seq
select id, child, lvl from c;

or add it to source data at first:

select id, lpad(' ', 4 * (level-1) ) || child child, level 
  from (select id, parent, child from temp union all
        select null, null, 100 from dual )
  start with child = 100
  connect by  parent = prior child

^{demo with all queries}