Oracle SQL's hierarchical query capabilities allow you to traverse tree-structured data stored in relational tables using the CONNECT BY and START WITH clauses. This post covers how to write hierarchical queries to navigate parent-child relationships in data like organizational charts, category trees, and bill-of-materials structures.
Introduction To Hierarchical Queries
Oracle SQL has hierarchical queries that are used specifically for data that is hierarchical. These queries allow you to arrange the rows of these tables in hierarchical order. Hierarchical data is data that contains a parent-child relationship.
- If a table contains hierarchical data then you can arrange the rows of this table in hierarchical order using HQ
- Hierarchical data in this context is where you have columns that are related to each other via a parent-child relationship, for example having an employee id and a manager id
- Examples of hierarchical data
- Organizational
- General Ledger
- Product Categories
- .....
CONNECT BY PRIOR & START WITH
The connect by prior clause defines the parent and child columns in your table. The column immediately following the prior keyword is the child column.
- CONNECT BY PRIOR specifies the relationship between parent and child rows
- PRIOR specifies the child column and hence the direction of the query
- START WITH specifies the root row(s) of the hierarchy, if this is not specified all roots will be returned
select * from emp
start with empno = 7566
connect by prior mgr=empno;
LEVEL
- LEVEL is a pseudo column that can be used in the SELECT statement only when a CONNECT BY CLAUSE is used
- It shows the level in the hierarchy for each returned row
Frequently Asked Questions
What are hierarchical queries in Oracle SQL?
Hierarchical queries are Oracle SQL queries that use the CONNECT BY clause to traverse parent-child relationships in data, allowing you to retrieve hierarchically structured data (like organizational trees) in a meaningful, ordered way.
What is the difference between START WITH and CONNECT BY?
START WITH specifies the root row(s) where the traversal begins, while CONNECT BY defines the relationship between parent and child rows using the PRIOR keyword to reference the parent row's column value.