|
CONNECT BY to querying hierarchical data in Oracle
Getting data in hierarchical order may be a big task by writing pl/sql or
other programing code. Oracle made is easy with connect by clause. This feature
is not often found in other databases.
In the article below, explain the syntax and most common hierarchical queries.
In future I will add more examples in this article.
Syntax:
START WITH <condition>
CONNECT BY
[NOCYCLE]
<condition>
|
CONNECT BY PRIOR:
This is a condition that identifies the relationship between parent rows and
child rows of the hierarchy.
SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
|
What if you want to see data from the employees in a hierarchical order
starting with the employee whose name is 'King.' The LEVEL keyword is used to
show the level of the hierarchy.
The answer is, you can use "START WITH" to start hierarchy with employee name.
START WITH:
It specifies a condition that identifies the row(s) to be used as the root(s) of
a hierarchical query.
SELECT employee_name, manager_name, LEVEL
FROM employees
START WITH employee_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
|
This query returns rows from the table employees in a hierarchical order
starting with the employee whose name is 'King.' The LEVEL keyword is used to
show the level of the hierarchy.
A new keyword, 'SIBLINGS,' can be used to order all child rows of a given parent
by some criteria. The query below gives an example of SIBLINGS:
ORDER SIBLINGS BY:
SELECT employee_name, manager_name, dept_name
FROM employee, dept
WHERE employee.deptno = dept.deptno
START WITH employee_name = 'KING'
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY hire_date;
|
This query orders all employees reporting to a common manager by their hire
date.
| 
