Summary - Recursive CTE

Write a company org-chart report with a recursive CTE.

Summary - Recursive CTE

A recursive CTE references itself. It has an anchor and a recursive part, joined with UNION ALL.

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

Summary recursive CTE.

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.

Exercise Summary - Recursive CTE

Summary: 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 stops a recursive CTE?
- COMMIT
- ORDER BY
- LIMIT
- The WHERE condition
Show answer

The WHERE condition — A WHERE condition stops a recursive CTE.
Summary - Recursive CTE: match the terms

recursive

recursive
anchor

base
UNION ALL

combine
WHERE

stop

Next lesson: What is a window function?

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies