How Hierarchical SQL works

Employee ID	Manager ID
100
101	100
203	101
204	101
102	205

How it works

SEE Data, understand its traversal, it searches each branch till its leaf, when start by root.

1.      To start with node: ex. At 100, then use syntax: start with 100

2.      Level is pseudo column: which shows the level at which query is running

3.      Connect by : tag causes to connect two rows at two different levels.

4.      Prior is identifying: prior <cell> = current <cell value>

When we run

Select LEVEL, EMPLOYEE_ID, MANAGER_ID FROM EMPLOYEES

START WITH EMPLOYEE_ID =100

Employee ID	Manager ID
100

è SO THIS SQL WORKS ON TWO ROWS TOGETHER, SO NEXT ROW IS FOUND BY CONNECT BY TAG

è AS CURSOR/POINTER MOVES TO NEXT ROW BY USING START BY TAG

o   CURRENT ROW BECOMES PRIOR

o   NEXT ROW BECOMES CURRENT

Employee ID	Manager ID	ROW INDICATOR
100		PRIOR
101	100	CURRENT

CONNECT BY PRIOR

Employee ID	Manager ID	ROW INDICATOR
100		PRIOR
101	100	CURRENT

CONNECT BY PRIOR EMPLOYEE_ID =  [CURRENT] MANAGER_ID

Select LEVEL, EMPLOYEE_ID, MANAGER_ID FROM EMPLOYEES

START WITH  EMPLOYEE_ID =100

CONNECT BY PRIOR EMPLOYEE_ID =  [CURRENT] MANAGER_ID