SQL Recursive CTE

Recursive CTEs

A recursive CTE refers to itself. It starts from some rows, then keeps adding the rows linked to the ones it just found, which is how you walk a tree of any depth or generate a series.

Syntax

Order of execution: the anchor runs once → the recursive part runs on the rows found in the last round → repeat until a round finds no rows → the main query reads everything found

Code
WITH RECURSIVE name AS (
  SELECT columns FROM table_name WHERE start_condition       -- anchor
  UNION ALL
  SELECT t.columns FROM table_name t
  JOIN name n ON t.parent_id = n.id                          -- recursive part
  WHERE stop_condition
)
SELECT * FROM name;

Part	Meaning
anchor	the starting rows
recursive part	finds the next rows, using the rows from the last round
`UNION ALL`	adds each round to the result (`UNION` also works in PostgreSQL and MySQL, and drops repeated rows)
stop	the recursion ends when a round returns no rows

SQL Server and Oracle leave out the word RECURSIVE: a plain WITH may refer to itself. Both need UNION ALL, and Oracle also needs the column names in a list after the name, such as WITH n (x) AS (…).

Examples

Counting

The simplest recursion adds 1 until the condition stops it. The stop is checked on the last round's row, before adding 1, so x < 5 still lets 4 through to make 5.

Code
WITH RECURSIVE n AS (
  SELECT 1 AS x
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 5         -- the round with x = 4 adds 5; with x = 5 it adds nothing
)
SELECT x FROM n;

Walking up a tree

Start from one category and follow parent_category_id up to the top, counting the levels.

Code
WITH RECURSIVE up AS (
  SELECT category_id, category_name, parent_category_id, 0 AS steps_up
  FROM categories WHERE category_id = 13
  UNION ALL
  SELECT c.category_id, c.category_name, c.parent_category_id, up.steps_up + 1
  FROM categories c
  JOIN up ON c.category_id = up.parent_category_id
)
SELECT category_id, category_name, steps_up FROM up ORDER BY steps_up;

Building text as you go

Each round adds a name to a trail. The anchor's column decides the type, so cast it to text.

Code
WITH RECURSIVE up AS (
  SELECT category_id, parent_category_id, category_name::text AS trail
  FROM categories WHERE category_id = 13
  UNION ALL
  SELECT c.category_id, c.parent_category_id, up.trail || ' < ' || c.category_name
  FROM categories c
  JOIN up ON c.category_id = up.parent_category_id
)
SELECT trail FROM up;
 
-- Fails: the anchor's VARCHAR(60) doesn't match the text the recursive part builds
WITH RECURSIVE up AS (
  SELECT category_id, parent_category_id, category_name AS trail
  FROM categories WHERE category_id = 13
  UNION ALL
  SELECT c.category_id, c.parent_category_id, up.trail || ' < ' || c.category_name
  FROM categories c JOIN up ON c.category_id = up.parent_category_id
)
SELECT trail FROM up;

Generating dates

Recursion can make rows that aren't in any table, such as every day of a week.

Code
WITH RECURSIVE days AS (
  SELECT DATE '2025-10-01' AS day
  UNION ALL
  SELECT day + 1 FROM days WHERE day < DATE '2025-10-07'
)
SELECT day FROM days;

Remember

The anchor runs once; the recursive part runs again on each new round of rows.
Always have a stop: a WHERE limit or a tree that ends. Without one, PostgreSQL runs until the query times out (SQL Server stops with an error after 100 rounds).
The anchor's columns set the types: cast text you build to text.
Join the recursive part on the link you're following: child to parent to walk up, parent to child to walk down.

Practice

Everything Under Gadgets & Media (Medium)
Category Breadcrumbs (Hard)
Orders per Month of 2026, Zeros Included (Medium)

Recursive CTEs

A recursive CTE refers to itself. It starts from some rows, then keeps adding the rows linked to the ones it just found, which is how you walk a tree of any depth or generate a series.

Syntax

Order of execution: the anchor runs once → the recursive part runs on the rows found in the last round → repeat until a round finds no rows → the main query reads everything found

Code
WITH RECURSIVE name AS (
  SELECT columns FROM table_name WHERE start_condition       -- anchor
  UNION ALL
  SELECT t.columns FROM table_name t
  JOIN name n ON t.parent_id = n.id                          -- recursive part
  WHERE stop_condition
)
SELECT * FROM name;

Part	Meaning
anchor	the starting rows
recursive part	finds the next rows, using the rows from the last round
`UNION ALL`	adds each round to the result (`UNION` also works in PostgreSQL and MySQL, and drops repeated rows)
stop	the recursion ends when a round returns no rows

Examples

Counting

The simplest recursion adds 1 until the condition stops it. The stop is checked on the last round's row, before adding 1, so x < 5 still lets 4 through to make 5.

Code
WITH RECURSIVE n AS (
  SELECT 1 AS x
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 5         -- the round with x = 4 adds 5; with x = 5 it adds nothing
)
SELECT x FROM n;

Walking up a tree

Start from one category and follow parent_category_id up to the top, counting the levels.

Code
WITH RECURSIVE up AS (
  SELECT category_id, category_name, parent_category_id, 0 AS steps_up
  FROM categories WHERE category_id = 13
  UNION ALL
  SELECT c.category_id, c.category_name, c.parent_category_id, up.steps_up + 1
  FROM categories c
  JOIN up ON c.category_id = up.parent_category_id
)
SELECT category_id, category_name, steps_up FROM up ORDER BY steps_up;

Building text as you go

Each round adds a name to a trail. The anchor's column decides the type, so cast it to text.

Code
WITH RECURSIVE up AS (
  SELECT category_id, parent_category_id, category_name::text AS trail
  FROM categories WHERE category_id = 13
  UNION ALL
  SELECT c.category_id, c.parent_category_id, up.trail || ' < ' || c.category_name
  FROM categories c
  JOIN up ON c.category_id = up.parent_category_id
)
SELECT trail FROM up;
 
-- Fails: the anchor's VARCHAR(60) doesn't match the text the recursive part builds
WITH RECURSIVE up AS (
  SELECT category_id, parent_category_id, category_name AS trail
  FROM categories WHERE category_id = 13
  UNION ALL
  SELECT c.category_id, c.parent_category_id, up.trail || ' < ' || c.category_name
  FROM categories c JOIN up ON c.category_id = up.parent_category_id
)
SELECT trail FROM up;

Generating dates

Recursion can make rows that aren't in any table, such as every day of a week.

Code
WITH RECURSIVE days AS (
  SELECT DATE '2025-10-01' AS day
  UNION ALL
  SELECT day + 1 FROM days WHERE day < DATE '2025-10-07'
)
SELECT day FROM days;

Remember

The anchor runs once; the recursive part runs again on each new round of rows.
Always have a stop: a WHERE limit or a tree that ends. Without one, PostgreSQL runs until the query times out (SQL Server stops with an error after 100 rounds).
The anchor's columns set the types: cast text you build to text.
Join the recursive part on the link you're following: child to parent to walk up, parent to child to walk down.

Practice

Everything Under Gadgets & Media (Medium)
Category Breadcrumbs (Hard)
Orders per Month of 2026, Zeros Included (Medium)

SQLClimbing the Hierarchy

Syntax

Examples

Counting

Walking up a tree

Building text as you go

Generating dates

Remember

Practice

SQLClimbing the Hierarchy

Syntax

Examples

Counting

Walking up a tree

Building text as you go

Generating dates

Remember

Practice