- Author – Saurabh Gupta
In this post we will go through recursive query with one example.
The recursive query features enables to query hierarchical data such as an organisation chart or bill of materials ( check the raw materials used to make a product ). Using Recursive query data with in the single table can be scanned multiple times using a single SQL statement.
A recursive query is useful when relationships between data elements exist in multiple iterations. Recursive query consist of two parts a seeding query and a recursive query. Seeding query provides the initial records for which data needs to be iterated whereas Recursive query part helps in data iteration.
We will be using below syntax for recursive query :
Recursive query syntax :
WITH RECURSIVE < recursive_tablename > (<columnname 1>,<columnname 2>,….<columnname n>) AS ( <SELECT * from seed_table> UNION ALL <select * from recursive_tablename join seed_table> ) <SELECT * FROM recursive_tablename>;
1) Recursive_tablename is a derived recursive table defined using WITH RECURSIVE keyword.
2) Column names of recursive table is specified as <columname >. This column names will be finally displayed in the output.
3) Seeding query ( query which is used as the input for recursive logic ) is mentioned as the first select statement.
4) Second select statement is known as the recursive query and is joined with seed table for multiple iterations.
5) Third select statement finally gives the output of recursive logic.
Let’s consider a table, Employee ( ddl shown below ) which stores –
CREATE VOLATILE TABLE Employee ( EMP_ID INTEGER, MGR_ID INTEGER, EMP_NAME VARCHAR(100) ) ON COMMIT PRESERVE ROWS;
Employee Id in column EMP_ID , Employee’s manager Id in column MGR_ID and Employee Name in column EMP_NAME e.g. Zoya is an employee with Employee Id 28006 and her direct Manager Id is 28005 .
One day Zoya wants to find out her first level, second level managers in the hierarchy i.e. all the direct and indirect manager above her in the hierarchy. Since employee table stores only first level of hierarchy (i.e. the direct manager which is employee id 28005 ( Patrick ) in case of Zoya), to find the hierarchy she needs to query database multiple times .This can be achieved very easily using a recursive query within a single sql statement.
WITH RECURSIVE MGR_JRS (EMP_ID, MGR_ID, MGR_NAME, DEPTH) AS ( SELECT EMPL.EMP_ID, EMPL.MGR_ID, EMPL1.EMP_NAME AS MGR_NAME, 1 AS DEPTH FROM Employee empl inner join Employee empl1 on empl.mgr_id = empl1.emp_id WHERE empl.EMP_ID=28006 UNION ALL SELECT MGR_JRS.EMP_ID, EMPL.MGR_ID, empl1.EMP_NAME, MGR_JRS.DEPTH + 1 FROM MGR_JRS INNER JOIN Employee empl ON MGR_JRS.MGR_ID = EMPL.EMP_ID inner join Employee empl1 on empl.mgr_id = empl1.emp_id ) SELECT * FROM MGR_JRS;
Below points explains the above sql :
1) WITH RECURSIVE keyword specifies table MGR_JRS is a derived recursive table.
2) The first select statement :
SELECT EMPL.EMP_ID, EMPL.MGR_ID, EMPL1.EMP_NAME AS MGR_NAME, 1 AS DEPTH FROM Employee empl inner join Employee empl1 on empl.mgr_id = empl1.emp_id WHERE empl.EMP_ID=28006
gives the Manager name and manager employee Id for Employee ID 28006 ( for Zoya ). So the first level manager for Zoya identified by above sql is Patrick. Data retrieved using above sql is internally stored in derived table MGR_JRS. This is often referred as seeding query.
3) Second select joins Employee table to MGR_JRS table which is getting populated as part of recursive logic. This is often referred as recursive query.
SELECT MGR_JRS.EMP_ID, EMPL.MGR_ID, empl1.EMP_NAME, MGR_JRS.DEPTH + 1 FROM MGR_JRS INNER JOIN Employee empl ON MGR_JRS.MGR_ID = EMPL.EMP_ID inner join Employee empl1 on empl.mgr_id = empl1.emp_id
Since MGR_JRS has one record with MGR_ID 28005 , it joins with employee table where EMP_ID is 28003 and in turn saves one more row in MGR_JRS.
Now Zoya knows her second level manager is Lorena.
4) Each time a new row is added to MGR_JRS, the possibility exists for a new match and thus a higher level manager will be found in hierarchy.
5) It will recursively execute this join operation until it has found all managers for Zoya.
6) When the WITH table population is completed i.e. Step 5 is completed, the final SELECT will execute producing the below output.
In this case Zoya has four levels of manager above her in the organization chart. Thus, a complex requirement of multiple iteration is very easily solved using recursive query.
Following are not allowed in recursive portion of query but allowed in seeding portion :
- Subqueries and derived tables
- Aggregation and OLAP functions
- NOT IN and NOT EXISTS
- GROUP BY and extended grouping functions
TOP N function ,Cursors ,Stored procedures,Triggers are not permitted in recursive query
The recursive query feature enables multiple iterations against hierarchical data in a single SQL statement.
If you like the post do share it on your social networking sites with the below share button 🙂