Premium

Recursive CTE

Basic SQL
≈ 2 min read

A recursive CTE uses its own result

A normal CTE is calculated as a named result set. A recursive CTE can refer to itself and add new rows step by step until the next step produces nothing new.

Two parts of recursion

A recursive CTE has an anchor part that produces the first rows and a recursive part that builds the next rows from rows already produced. 

WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 5
)
SELECT n
FROM numbers;

The anchor part produces the number 1. Then the recursive part takes the current value and adds the next one while n < 5 allows recursion to continue. The result is the numbers from 1 through 5.

Where it is used

The main practical use case is hierarchical data: category trees, organizational structures, comments with parent messages, and other “parent → child” models.

Recursion needs a stopping condition. An incorrect recursive part can keep generating rows for too long or indefinitely from the query's logical point of view. Always make sure that each step actually moves the computation toward termination.
Back Next