What is a recursive CTE?

Learn the idea of a self-calling CTE.

What is a recursive CTE?

A recursive CTE is a CTE that references itself. Used for hierarchies and trees.

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

This query counts from 1 to 5.

WITH RECURSIVE defines a self-referencing temporary result set (CTE) named countdown; the query after it uses it like a table. All columns are selected from countdown.

What is a recursive CTE? — details

A recursive CTE has an anchor and a recursive part. They are joined with UNION ALL.

It is used for tasks where you don't know the number of steps in advance, such as category trees, management chains or generating a sequence from 1 to N.

Exercise What is a recursive CTE?

Write a RECURSIVE CTE that counts from 1 to 5.

Show example solution

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

Test yourself

What does a recursive CTE combine?
- INTERSECT
- JOIN
- UNION ALL
- EXCEPT
Show answer

UNION ALL — A recursive CTE combines with UNION ALL.

Add RECURSIVE

WITH ____ countdown(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM countdown WHERE n < 5
)
SELECT * FROM countdown;

Show answer

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

What is a recursive CTE: match the terms

recursive

recursive
anchor

base
UNION ALL

combine
hierarchy

tree

Next lesson: Anchor and recursive parts

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies