0Pricing
SQL Academy · Lesson

How Recursive CTEs Work

Base case plus recursive step.

How Recursive CTEs Work is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

Counting from 1 to 5

The simplest recursive CTE counts numbers. The base case seeds the value 1. The recursive step adds 1 each iteration. The WHERE clause inside the recursive step acts as the termination condition — without it the query would run forever.

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

Step-by-Step Execution

Here is how the engine processes the counter CTE iteration by iteration:

Iteration 0 (base case): returns {1}.

Iteration 1: applies recursive step to {1}, returns {2}.

Iteration 2: applies recursive step to {2}, returns {3}.

Iteration 3, 4: returns {4}, then {5}.

Iteration 5: WHERE n < 5 is false for n=5, so zero rows returned. Query ends.

All accumulated rows — 1, 2, 3, 4, 5 — are the final result.

Setting Up a Hierarchy Table

Recursive CTEs shine on self-referencing tables. Let's create an employees table where each employee has an optional manager_id pointing back to the same table.

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

Traversing the Hierarchy

Now we can walk the entire reporting chain starting from the CEO (Alice, id=1). The base case selects Alice; the recursive step finds all employees whose manager_id matches an id already in the CTE.

The result includes every employee reachable from Alice, no matter how deep the tree goes.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

Tracking the Path

A common enhancement is building a path string that shows the full chain from root to each node. We concatenate names separated by ' -> ' as we recurse deeper.

This makes it easy to display breadcrumb-style navigation or debug deep hierarchies.

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Limiting Recursion Depth

Deep or circular data can make a recursive CTE run for a very long time. Two safe practices:

1. Track depth and add a WHERE clause — WHERE depth < 10 ensures you never go past 10 levels.

2. Use a cycle-detection column — some databases (PostgreSQL 14+) offer CYCLE syntax to detect repeated node visits automatically.

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

UNION vs UNION ALL in Recursive CTEs

The recursive step almost always uses UNION ALL, not UNION. Here is why:

UNION deduplicates rows after every iteration by comparing the entire result set — this is extremely expensive and can change semantics for graphs where the same node is legitimately reached via multiple paths.

UNION ALL keeps all rows without deduplication, which is both faster and correct for tree traversal. Use UNION only when you have a specific need to eliminate duplicates and understand the performance cost.

Generating a Date Series

Recursive CTEs are also handy for generating sequences of dates. This example produces every day of a given week — a pattern often used to build calendar reports or fill gaps in time-series data.

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

Finding All Subordinates of One Manager

You can seed the base case with any specific node — not just the root. Here we start from Bob (id=2) and find everyone who reports to him directly or indirectly.

This pattern is useful for permission checks, subtree aggregations, or scoping dashboards to a single department.

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

Quick Check

Test your understanding of how recursive CTEs work.

Lesson Recap

In this lesson you learned how recursive CTEs work:

Structure: every recursive CTE has a base case (starting rows) joined to a recursive step (self-referencing SELECT) with UNION ALL.

Termination: the engine repeats the recursive step and accumulates results until the step returns zero rows.

Common uses: walking org charts and folder trees, generating number or date sequences, computing paths, and finding all nodes in a subtree.

Safety tips: always include a termination condition (depth limit or cycle guard) and prefer UNION ALL over UNION for performance.

Frequently asked questions

Is the “How Recursive CTEs Work” lesson free?

Yes — the full text of “How Recursive CTEs Work” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “How Recursive CTEs Work”?

Base case plus recursive step. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “How Recursive CTEs Work” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. How Recursive CTEs Work
  2. Walking a Category Tree
  3. Generating Series and Sequences
  4. Avoiding Infinite Loops
← Back to SQL Academy