Oracle Hierarchical Query Operators
A hierarchical query is a type of SQL query that handles hierarchical model data. There are a special case of more general recursive fixpoint queries, which compute transitive closures. In standard SQL:1999 hierarchical queries are implemented by way of recursive common table expressions.
An alternative syntax is the non-standard CONNECT BY construct; it was introduced by Oracle in the 1980s. Prior to Oracle 10g, the construct was only useful for traversing acyclic graphs because it returned an error on detecting any cycles.
In Oracle PRIOR and CONNECT_BY_ROOT, operators are valid only in hierarchical queries.
PRIOR Operator :
- One expression in the CONNECT BY condition must be qualified by the PRIOR operator.
- If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions.
- PRIOR evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
- PRIOR is most commonly used when comparing column values with the equality operator.
PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case, Oracle detects the loop at a run time and returns an error.
- CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries.
- When we qualify a column with this operator, Oracle returns the column value using data from the root row.
- This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.
Restriction: You cannot specify this operator in the START WITH condition or the CONNECT BY condition.
Hierarchical Query Examples
The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:
Sample table: employees
SELECT employee_id, first_name, last_name, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER_ID ----------- -------------------- ------------------------- ---------- 101 Neena Kochhar 100 108 Nancy Greenberg 101 109 Daniel Faviet 108 113 Luis Popp 108 112 Jose Manuel Urman 108 111 Ismael Sciarra 108 110 John Chen 108 205 Shelley Higgins 101 206 William Gietz 205 ...............
The following example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:
Sample table: employees
SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL ----------- ------------------------- ---------- ---------- 101 Kochhar 100 1 108 Greenberg 101 2 109 Faviet 108 3 113 Popp 108 3 112 Urman 108 3 111 Sciarra 108 3 110 Chen 108 3 205 Higgins 101 2 206 Gietz 205 3 204 Baer 101 2 203 Mavris 101 2 200 Whalen 101 2 201 Hartstein 100 1 202 Fay 201 2 ...............
Note: In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will override the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.
- Exercises: Weekly Top 16 Most Popular Topics
- SQL Exercises, Practice, Solution - JOINS
- SQL Exercises, Practice, Solution - SUBQUERIES
- Java Array: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : Conditional Statement
- HR Database - SORT FILTER: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : String
- Python Data Types: Dictionary - Exercises, Practice, Solution
- Python Programming Puzzles - Exercises, Practice, Solution
- C++ Array: Exercises, Practice, Solution
- C# Sharp Basic Algorithm: Exercises, Practice, Solution
- Python Lambda - Exercises, Practice, Solution
- Python Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools