In Oracle PL/SQL, the START WITH clause appears in hierarchical SQL queries and is used to query data with Master-Detail relationships. It marks a node in the tree which is considered as the starting node or level of the tree structured data. A scalar value or subquery can be used to assign the root of the query.
It is mandatory for use with the CONNECT_BY clause, which indicates relationship between a node and subnode. The parent row can be identified with the PRIOR keyword.
Note that START WITH clause is an optional clause in hierarchical queries.
START WITH [COLUMN] = [ROOT NODE VALUE]
CONNECT BY [CONDITION]
The SQL query below lists Employees reporting to their Managers at different levels.
SELECT level, LPAD(ename,level*5,' ') ename
start with empno=100
CONNECT_BY by nocycle prior empno = mgr
9 rows selected.