Hierarchical Query – Oracle

Oracle’s START WITH and CONNECT BY clauses in the SELECT statement traverse a hierarchy.
Without this feature, n number of self-joins (n = max_levels) would be required.

ORDER SIBLINGS BY ename will preserve the hierarchy and also sort the ename within each level.

Prior to Oracle 10g, a circular loop in the hierarchy would return an error, ” ORA-01436: CONNECT BY loop in user data”.
In Oracle 10g, It can be avoided by the NOCYCLE
connect by nocycle prior empno = mgr

Syntax
Oracle 9i

Oracle 10g

Oracle 11g