SQL Academy · Lesson

How Recursive CTEs Work

Base case plus recursive step.

What Is a Recursive CTE?

A recursive CTE is a Common Table Expression that references itself. It lets you write queries that repeat a step until a condition is met — similar to a loop, but expressed as pure SQL.

Recursive CTEs are defined with the WITH RECURSIVE keyword and are ideal for traversing hierarchical or graph-like data such as org charts, folder trees, and bill-of-materials structures.

The Two-Part Structure

Every recursive CTE has exactly two parts separated by UNION ALL:

1. Base case — a non-recursive SELECT that returns the starting rows.

2. Recursive step — a SELECT that joins the CTE back to itself, producing the next level of rows.

The engine keeps running the recursive step and accumulating results until it produces zero new rows.

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

All lessons in this course

← Back to SQL Academy