A recursive named query provides a way to iteratively query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy.
A recursive query cannot reference another recursive query, either directly or indirectly.
A recursive named query has three execution phases:
- Create an initial nonrecursive, or seed, result set.
- Recurse the intermediate result sets based on the seed result set until no new rows are added to the temporary named result set.
- Execute a last query on the temporary named result set to return the final result set.
Consider this employee table:
CREATE TABLE employee ( employee_number INTEGER, manager_employee_number INTEGER, last_name CHARACTER(20), first_name VARCHAR(30));
The table represents an organizational structure of employee-manager relationships. The employee table is similar to this organization chart.
The following recursive query retrieves the employee numbers of all employees who directly or indirectly report to the manager who has an employee_number value of 801:
WITH RECURSIVE temp_table (employee_number) AS (SELECT root.employee_number FROM employee AS root WHERE root.manager_employee_number = 801 UNION ALL SELECT indirect.employee_number FROM temp_table AS direct, employee AS indirect WHERE direct.employee_number = indirect.manager_employee_number ) SELECT * FROM temp_table ORDER BY employee_number;
In the example, temp_table is a temporary named result set that can be referred to in the FROM clause of the recursive statement.
The initial result set is established in temp_table by the nonrecursive, or seed, statement and contains the employees that report directly to the manager with an employee_number of 801:
SELECT root.employee_number FROM employee AS root WHERE root.manager_employee_number = 801
The recursion takes place by joining each employee in temp_table with employees who report to the employees in temp_table. The UNION ALL adds the results to temp_table.
SELECT indirect.employee_number FROM temp_table AS direct, employee AS indirect WHERE direct.employee_number = indirect.manager_employee_number
Recursion stops when no new rows are added to temp_table.
The final query is not part of the recursive WITH request modifier and extracts the employee information from temp_table:
SELECT * FROM temp_table ORDER BY employee_number;
The results of the recursive query are as follows:
employee_number --------------- 1001 1002 1003 1004 1006 1008 1010 1011 1012 1014 1015 1016 1019