Recursive Common Table Expressions Overview

Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. CTEs first appeared in the SQL standard in 1999, and the first implementations began appearing in 2007.

There are two kinds of CTEs:

• Non-recursive;

• Recursive, which this article covers.

SQL is generally poor at recursive structures.

CTEs permit a query to reference itself. A recursive CTE will repeatedly execute subsets of the data until it obtains the complete result set. This makes it particularly useful for handing hierarchical or tree-structured data. avoids infinite loops.

Syntax example

signifies a recursive CTE. It is given a name, followed by a body (the main query) as follows:

Computation

Given the following structure:

First execute the anchor part of the query:

Next, execute the recursive part of the query:

Summary

1. Compute anchor_data.

2. Compute recursive_part to get the new data.

3. If (new data is non-empty) goto 2.

CAST to avoid truncating data

As currently implemented by MariaDB and by the SQL Standard, data may be truncated if not correctly cast. It is necessary to the column to the correct width if the CTE's recursive part produces wider values for a column than the CTE's nonrecursive part. Some other DBMS give an error in this situation, and MariaDB's behavior may change in future - see . See the .

Examples

Transitive closure - determining bus destinations

Sample data:

Now, we want to return the bus destinations with New York as the origin:

The above example is computed as follows:

First, the anchor data is calculated:

• Starting from New York.

• Boston and Washington are added.

Next, the recursive part:

• Starting from Boston and then Washington.

• Raleigh is added.

• UNION excludes nodes that are already present.

Computing paths - determining bus routes

This time, we are trying to get bus routes such as “New York -> Washington -> Raleigh”.

Using the same sample data as the previous example:

CAST to avoid data truncation

In the following example, data is truncated because the results are not specifically cast to a wide enough type:

Explicitly use to overcome this:

_{This page is licensed: CC BY-SA / Gnu FDL}