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