The WITH statement modifier is ANSI SQL:2011 compliant.
Other SQL dialects support similar non‑ANSI standard statements with names such as:
The nonrecursive WITH statement modifier is similar to a derived table. A derived table defines a named temporary result set from which the query can select data. Similarly, nonrecursive WITH defines a temporary named result set from which the SELECT statement can select data.
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;
The WITH RECURSIVE statement modifier provides a way to iteratively query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy.
WITH RECURSIVE has three execution phases:
1 Create an initial nonrecursive, or seed, result set.
2 Recurse the intermediate result sets based on the seed result set until no new rows are added to the temporary named result set.
3 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
The rules and restrictions are:
Because UNION ALL permits duplicate rows, it is not a true set operator or a true relational operator. Mathematical sets cannot contain duplicates.
This request produces the same results as the request that specifies a nonrecursive WITH statement modifier in “Nonrecursive WITH Statement Modifier” on page 53:
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;
Teradata Database does not support recursive queries for these forms of embedded SQL:
These statements cannot be preceded within a WITH or WITH RECURSIVE statement modifier:
You can specify the temporary result set created by a WITH statement modifier subquery as input to a FROM clause table function. See “Example 2: WITH Statement Modifier Result Set as Input to a Table Function” on page 60.
If the data hierarchy is cyclic, or if the recursive statement specifies a bad join condition, a recursive query can produce a request that never completes with a finite result.
In this context, a bad join is defined as a join that contains one or more of these errors.
Consider the employee table defined in “WITH RECURSIVE Statement Modifier” on page 54.
The following statement specifies an incorrect join condition in the recursive statement. The join condition (WHERE indirect.employee_number IN (1003, 1004) is not correct because the result is always true.
WITH RECURSIVE temp_table (employee_id, level) AS (
SELECT root.employee_number, 0 AS level
FROM employee AS root
WHERE root.employee_number = 1003
UNION ALL
SELECT direct.employee_id, direct.level + 1 ←recursive statement
FROM temp_table AS direct, employee AS indirect
WHERE indirect.employee_number IN (1003,1004)
)
SELECT *
FROM temp_table
ORDER BY level;
The result set returned by this query is as follows:
employee_id level
----------- -----
1003 0
1003 1
1003 1
1003 2
1003 2
1003 2
1003 2
1003 3
1003 3
1003 3
1003 3
1003 3
1003 3
1003 3
1003 3
... ...
and so on infinitely.
The best practice is to control the depth of the recursion as follows:
The following example adds a join condition (AND direct.level < 2) to the recursive statement in the previous query to limit the number of levels of recursion.
WITH RECURSIVE temp_table (employee_id, level) AS (
SELECT root.employee_number, 0 AS level
FROM employee AS root
WHERE root.employee_number = 1003
UNION ALL
SELECT direct.employee_id, direct.level+1
FROM temp_table AS direct, employee AS indir
WHERE indir.employee_number IN (1003,1004)
AND direct.level < 2
)
SELECT *
FROM temp_table
ORDER BY level;
Note that the data type of the numeric literal that you specify for the initial value of the depth control column is the smallest data type that can contain the value.
For example, in the preceding query, the data type of the numeric literal 0 is BYTEINT because it is the smallest type that can fit the value 0.
The data type of the initial value of the depth control column limits the number of levels of recursion to the maximum value that the data type can represent.
For example, the maximum value of a BYTEINT is 127. If you need more than 127 levels of recursion, you must cast the numeric literal that you specify for the initial value of the depth control column to a larger type.