Consider these table definitions:
CREATE TABLE product ( product_id INTEGER, on_hand INTEGER); CREATE TABLE stocked ( store_id INTEGER, product_id INTEGER, quantity INTEGER);
The following statement uses a nonrecursive WITH statement modifier to define a temporary named result set called orderable_items that is built from the select expression that follows the AS keyword:
WITH orderable_items (product_id, quantity) AS ( SELECT stocked.product_id, stocked.quantity FROM stocked, product WHERE stocked.product_id = product.product_id AND product.on_hand > 5 ) SELECT product_id, quantity FROM orderable_items WHERE quantity < 10;
The same results are produced by this statement using a derived table:
SELECT product_id, quantity FROM (SELECT stocked.product_id, stocked.quantity FROM stocked, product WHERE stocked.product_id = product.product_id AND product.on_hand > 5) AS orderable_items WHERE quantity < 10;
RECURSIVE Named Query in a WITH Modifier
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
WITH Modifiers
- The only set operator that can appear in a recursive named query within a WITH modifier is UNION ALL.
- The following elements cannot appear within a WITH or WITH RECURSIVE modifier:
- WITH or WITH RECURSIVE modifier
- TOP n operator
- User-defined functions
- The following elements cannot appear within a recursive statement in a WITH RECURSIVE modifier:
- NOT IN or NOT EXISTS logical predicate
- Aggregate functions
- Ordered analytical functions
- GROUP BY clause
- HAVING clause
- DISTINCT clause
- Subqueries
- Derived tables
- You cannot specify a WITH modifier in the definitions of any of these database objects:
- Triggers
- Stored procedures
- A recursive named query that does not have a recursive statement works like a nonrecursive named query.
This request produces the same results as the request that specifies a nonrecursive named query in the WITH modifier:
WITH RECURSIVE orderable_items (product_id, quantity) AS ( SELECT stocked.product_id, stocked.quantity FROM stocked, product WHERE stocked.product_id = product.product_id AND product.on_hand > 5) SELECT product_id, quantity FROM orderable_items WHERE quantity < 10;