17.05 - Using Recursive Queries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
January 2021
English (United States)
Last Update

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:
  • Initialization
  • Recursion, or repeated iteration of the logic through the hierarchy
  • Termination
Similarly, a recursive query has three execution phases:
  • Initial result set
  • Iteration based on the existing result set
  • Final query to return the final result set

Ways to Specify 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.

For a complete description of the recursive query feature, with examples that illustrate how it is used and its restrictions, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

For information on WITH RECURSIVE clause, see WITH Modifier.

For information on recursive views, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

Recursive Query Performance Considerations

Following are general guidelines regarding the performance impact of recursive queries:
  • Using a recursive query shows a significant performance improvement over using temporary tables with a stored procedures. In most cases, there is a highly significant improvement.
  • Using the WITH RECURSIVE clause has basically the same or equivalent performance as using the RECURSIVE VIEW.
  • In using a recursive query, it is important to put depth limits on the recursion to prevent infinite recursion when there are cycles in the underlying data.