Recursive SQL with CTE (common table expressions)

A recursive common table expression is a powerful SQL construct that allows you to perform complex data manipulation and retrieval. It is particularly useful for working with hierarchical or tree-structured data.

With recursive CTE, the query can refer to its own output.

Table of Contents

The basis: CTE (non recursive)

WITH my_cte AS (
    SELECT column1, column2
    FROM my_table
)
SELECT *
FROM my_cte;

The WITH clause allows the creation of auxiliary statements, to be used within a larger query.

These CTEs essentially define temporary tables that are only valid for the duration of a single query.

The WITH clause can contain one or more auxiliary statements, which can be a SELECT, INSERT, UPDATE, or DELETE statement.

The WITH clause is then attached to a primary statement, which can also be a SELECT, INSERT, UPDATE, or DELETE statement.

CTE recursive – Template

WITH RECURSIVE expression_name (column_list)
AS
(
    -- Anchor member
    initial query  
    UNION ALL
    -- Recursive member 
    recursive query  
)
SELECT *
FROM   expression_name

A recursive CTE consists of two parts:

An anchor member:
The base result set of the CTE
A recursive member:
References the CTE itself and builds upon the result set of the anchor member.
The recursive member is executed repeatedly until it returns an empty result set. At each iteration, the result set of the previous iteration is used as input for the next iteration.

Basic example

The sum of the first 10 numbers:

WITH RECURSIVE nb (n) AS (
    VALUES (1)
  UNION 
    SELECT n+1 FROM nb WHERE n < 10
)
SELECT sum(n) FROM nb;

Explanations:

nb : CTE name
VALUES (1)
The base result set, contains only the value 1.
SELECT n+1 FROM nb WHERE n < 10
The recursive query, which references the CTE itself.
At the first iteration, the result set contains the values 1 and 2.
At the second iteration, the result set contains the values 1, 2 and 3.
…

More complex example

We have a “relationship” table which contains the relationship parent / child.

Get all ancestors of “andré”:

WITH RECURSIVE descent (child) AS (
    select child from relationship where parent = 'andré'
  UNION 
    SELECT relationship.child FROM descent join relationship on descent.child = relationship.parent 
)
SELECT * FROM descent;

Explanations:

select child from relationship where parent = ‘andré’:
Select André’s children
SELECT relationship.child FROM descent join relationship on descent.child = relationship.parent
Recursively selects the children of the selection.

You can use our MySQL sandbox to test yourself: Mysql Playground.

Conclusion

Recursive CTEs are quite easy to write, yet they can yield powerful queries. They allow you to avoid having to write recursive code that executes multiple SQL queries, instead of just writing a simple, readable SQL query.