Definition:
In a table containing hierarchical data, rows can be selected in a hierarchical (parent->child) order using the START WITH/CONNECT BY clause:
(START WITH condition_1) CONNECT BY condition_2
The START WITH clause is optional. It specifies the rows that are the root(s) of the hierarchical query. If this clause is omitted Oracle uses all rows in the table as root rows. The START WITH condition can contain a subquery but cannot contain a subquery expression.
The CONNECT BY clause defines the relationship between the parent rows and the child rows of the hierarchy. The connect_by_condition can be any condition or predicate, but it must use the PRIOR operator to refer to the parent row.
Example Usage:
SELECT user_id, last_name, manager_id, LEVEL
FROM users
CONNECT_BY PRIOR user_id = manager_id;
This will return the user_id, last_name, manager_id and the level in the tree for the user hierarchy.
- CONNECT_BY_ROOT is a unary operator valid only in hierarchical queries. When a column is qualified with this operator, Oracle will return the column value using data from the root row.
- CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT_BY condition. Otherwise it returns 0.
- The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor, otherwise it returns 0. You must use the NOCYCLE parameter of the CONNECT_BY clause to use CONNECT_BY_ISCYCLE.
