For any specified row, its parent node and its child nodes are determined by the CONNECT BY clause. The CONNECT BY clause must consist of two expressions compared with the equals (=) operator. One of these two expressions must be preceded by the keyword PRIOR.
To determine the child nodes of any specified row, follow these steps:
- Evaluate parent_expr on the specified row.
- Evaluate child_expr on any other row resulting from the evaluation of table_expression.
- If parent_expr = child_expr, this row is a child node of the specified parent row.
- Repeat the process for all remaining rows in table_expression. All rows that satisfy the equation in step 3 are the child nodes of the specified parent row.
By repeating this process, you can regard each child node found in the preceding steps as a parent and build an inverted tree of nodes. The process is completed when the final set of child nodes has no child nodes. These nodes are the leaf nodes.
A SELECT statement that includes a CONNECT BY clause includes the START WITH clause. The START WITH clause determines the rows that are the root nodes. For example, the rows are the initial parent nodes on which the preceding algorithm is used. For more information, see the next topic.