Hierarchical queries v15
A hierarchical query is a type of query that returns the rows of the result set in a hierarchical order based on data forming a parent-child relationship. A hierarchy is typically represented by an inverted tree structure. The tree is made up of interconnected nodes. Each node can be connected to none, one, or multiple child nodes. Each node is connected to one parent node except for the top node, which has no parent. This node is the root node. Each tree has exactly one root node.
Nodes that don’t have any children are called leaf nodes. A tree always has at least one leaf node, for example, the trivial case where the tree is made up of a single node. In this case, the node is both the root and the leaf.
In a hierarchical query, the rows of the result set represent the nodes of one or more trees.
Note
A single given row can appear in more than one tree and thus appear more than once in the result set.
Syntax
The hierarchical relationship in a query is described by the CONNECT BY
clause, which forms the basis of the order in which rows are returned in the result set. This example shows the context of where the CONNECT BY
clause and its associated optional clauses appear in the SELECT
command:
select_list
is one or more expressions that make up the fields of the result set. table_expression
is one or more tables or views from which the rows of the result set originate. other
is any additional legal SELECT
command clauses.
CONNECT BY
specifies the relationship between a parent record and its child records of the hierarchy.
You can specify the
PRIOR
keyword with only one expression in a condition in theCONNECT BY
clause. You must specify it with either right or left expression to refer to a parent row in a hierarchical query. For example:If the
CONNECT BY condition
is compound, only one condition requires thePRIOR
keyword, although there can be multiplePRIOR
conditions.You can use the
PRIOR
keyword to evaluate the parent row in a parent-child relationship in a hierarchical query.You can't use
PRIOR
keyword with unary expressions.You can use the
PRIOR
keyword to add a pseudo-column for each record corresponding to an expression in a target list. The pseudo-column represents a parent record's actual value when referenced in a child query using aPRIOR
expression.
Use the CONNECT_BY_ROOT
operator to further enhance the functionality of the CONNECT BY [PRIOR]
condition. This operator returns not only immediate parent rows but also all ancestors rows in the hierarchy. For more information, see Retrieving the root node with CONNECT_BY_ROOT.
Examples
This example uses the CONNECT BY
clause with PRIOR
to define a relationship between employees and managers. Using the LEVEL
value, the employee names are indented to further emphasize the depth in the hierarchy of each row.
The output from this query is:
This example adds the ORDER BY
clause and a compound condition with AND
operator in a CONNECT BY
clause to show employee and manager hierarchy:
The output from this query is:
This example uses the PRIOR
keyword to return the employee name and manager name for each employee:
The output from this query is:
This example uses a CONNECT BY
clause with an optional PRIOR
statement to show the level value:
The output from this query is:
defining_the_parent_child_relationship selecting_the_root_nodes organization_tree_in_the_sample_application node_level ordering_the_siblings retrieving_the_root_node_with_connect_by_root retrieving_a_path_with_sys_connect_by_path