# 15.00 - Recursive Queries - Teradata Database

prodname
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

## Recursive Queries

A recursive query is a way to query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy.

Recursion is typically characterized by three steps:

1 Initialization

2 Recursion, or repeated iteration of the logic through the hierarchy

3 Termination

Similarly, a recursive query has three execution phases:

1 Create an initial result set.

2 Recursion based on the existing result set.

3 Final query to return the final result set.

### Specifying a Recursive Query

You can specify a recursive query by:

• Preceding a query with the WITH RECURSIVE clause
• Creating a view using the RECURSIVE clause in a CREATE VIEW statement
• ### Using the WITH RECURSIVE Clause

Consider the following employee table:

`   CREATE TABLE employee`
`      (employee_number INTEGER`
`      ,manager_employee_number INTEGER`
`      ,last_name CHAR(20)`
`      ,first_name VARCHAR(30));`

The table represents an organizational structure containing a hierarchy of employee-manager data.

The following figure depicts what the employee table looks like hierarchically.

The following recursive query retrieves the employee numbers of all employees who directly or indirectly report to the manager with employee_number 801:

`   WITH RECURSIVE temp_table (employee_number) AS`
`   ( SELECT root.employee_number`
`     FROM employee root`
`     WHERE root.manager_employee_number = 801`
`   UNION ALL `
`     SELECT indirect.employee_number`
`     FROM temp_table direct, employee 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 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 direct, employee 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 clause and extracts the employee information out of temp_table:

`   SELECT * FROM temp_table ORDER BY employee_number;`

Here are the results of the recursive query:

`employee_number`
`---------------`
`           1001`
`           1002`
`           1003`
`           1004`
`           1006`
`           1008`
`           1010`
`           1011`
`           1012`
`           1014`
`           1015`
`           1016`
`           1019`

### Using the RECURSIVE Clause in a CREATE VIEW Statement

Creating a view using the RECURSIVE clause is similar to preceding a query with the WITH RECURSIVE clause.

Consider the employee table that was presented in “Using the WITH RECURSIVE Clause” on page 120. The following statement creates a view named hierarchy_801 using a recursive query that retrieves the employee numbers of all employees who directly or indirectly report to the manager with employee_number 801:

`   CREATE RECURSIVE VIEW hierarchy_801 (employee_number) AS`
`   ( SELECT root.employee_number`
`     FROM employee root`
`     WHERE root.manager_employee_number = 801 `
`   UNION ALL `
`     SELECT indirect.employee_number`
`     FROM hierarchy_801 direct, employee indirect`
`     WHERE direct.employee_number = indirect.manager_employee_number`
`   );`

The seed statement and recursive statement in the view definition are the same as the seed statement and recursive statement in the previous recursive query that uses the WITH RECURSIVE clause, except that the hierarchy_801 view name is different from the temp_table temporary result name.

To extract the employee information, use the following SELECT statement on the
hierarchy_801 view:

`   SELECT * FROM hierarchy_801 ORDER BY employee_number;`

Here are the results:

`employee_number`
`---------------`
`           1001`
`           1002`
`           1003`
`           1004`
`           1006`
`           1008`
`           1010`
`           1011`
`           1012`
`           1014`
`           1015`
`           1016`
`           1019`

### Depth Control to Avoid Infinite Recursion

If the hierarchy is cyclic, or if the recursive statement specifies a bad join condition, a recursive query can produce a runaway query that never completes with a finite result. The best practice is to control the depth of the recursion:

• Specify a depth control column in the column list of the WITH RECURSIVE clause or recursive view
• Initialize the column value to 0 in the seed statements
• Increment the column value by 1 in the recursive statements
• Specify a limit for the value of the depth control column in the join condition of the recursive statements
• Here is an example that modifies the previous recursive query that uses the WITH RECURSIVE clause of the employee table to limit the depth of the recursion to five cycles:

`   WITH RECURSIVE temp_table (employee_number, depth) AS`
`   ( SELECT root.employee_number, 0 AS depth`
`     FROM employee root`
`     WHERE root.manager_employee_number = 801 `
`   UNION ALL `
`     SELECT indirect.employee_number, direct.depth+1 AS newdepth`
`     FROM temp_table direct, employee indirect`
`     WHERE direct.employee_number = indirect.manager_employee_number`
`     AND newdepth <= 5`
`   )`
`   SELECT * FROM temp_table ORDER BY employee_number;`

### Related Topics

 For details on … See … recursive queries “WITH RECURSIVE” in SQL Data Manipulation Language. recursive views “CREATE VIEW” in SQL Data Definition Language.