HIERARCHICAL QUERIES IN ORACLE WITH TEAMSQL
Standard SQL doesn’t support hierarchical or recursive queries. However, in the relational data model we usually create tables with an intrinsic recursive nature. Many database engines add proprietary extensions to SQL to support recursive queries. In this article, we will explain how to query these kinds of tables in Oracle using TeamSQL.
There are some different clauses in Oracle to use in hierarchical or recursive queries. We will cover the most important of them. We will try to give first a real-life example of the concept.
We will use the following table employee with a hierarchical structure based on manager_id and employee_id. Using this table we will create some hierarchical queries in the rest of the article. The next image of TeamSQL shows the table content.
Our first query will be to obtain all the records with the employee name and its manager name.
ORACLE CLAUSES: CONNECT BY, START WITH AND PRIOR
To build the hierarchical query second most important part is the use of connecting by and prior keywords. These are two mandatory keywords to establish hierarchy. The hierarchy will be built like one row is the parent of another row and Start with keyword defines the ancestor. Here is the real-life example; if the user wants to see the manager name of an employee, then how that query will work?
The next image of TeamSQL shows the complete top to bottom hierarchy:
Let’s explain the clauses used in the above example:
The PRIOR clause is a unary operator which is used or indicates that “father of” the records or first record. Note we use PRIOR “ename” to refer to the column “ename” of the father’s record.
CONNECT BY define a kind of “join condition” between two records. The employee record and the boss record. We need to put PRIOR on the boss side of the condition. In our example, we use CONNECT BY PRIOR employee_id = manager_id.
START WITH clause is used to define from which record the hierarchy starts. In the above example we have started hierarchy with the employee whose manager_id is null. However, we can start to traverse the hierarchy at any point. For example, if we want to obtain all employees depending on one middle manager.
LEVEL is a pseudo column which gives the user the idea about the depth of the hierarchy. The record identified in START WITH clause has LEVEL 1
SHOW RECORDS IN A HIERARCHICAL FORM
The following query uses the LPAD function and the LEVEL pseudo column to show the records in hierarchical structure indented.
The previous query uses the ORDER SIBLINGS BY clause to show the result set ordered by the LEVEL pseudo column and when the records have the same LEVEL value, then ordered by the column specified after ORDER SIBLINGS BY.
BOSS CHAIN OF AN EMPLOYEE
In some cases, we can need to show the complete chain with all the managers in the middle. In this case, we will use the function SYS_CONNECT_BY_PATH(column_name, delimiter)
TOP CHAIN ELEMENT SPECIAL CASES
In our example table, the highest employee in the hierarchy has a NULL value as manager_id. However, in other cases, we can have a different way to identify a top hierarchy element. For example, we can use the same value for employee_id and manager_id. If we define the highest element with a non-null value, we can fall in a non-ending cycle. Let’s change the manager_id from NULL to 2 for employee_id 2, and see what happen in Oracle
The previous error can be avoided if we add the clause NOCYCLE to the CONNECT BY clause as we can see in the following image:
In above examples, we covered hierarchical queries in Oracle using TeamSQL with different examples. I hope you like this article!