Summary - Recursive CTE
- SQL course
- Advanced
- Lesson 12/81
- 5 min
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.
Practice Summary - Recursive CTE
Summary: write a recursive CTE that counts from 1 to 5.
Starter code
-- summary recursive
WITH RECURSIVESample solution
WITH RECURSIVE countdown(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM countdown WHERE n < 5
)
SELECT * FROM countdown;Expected output
n 1 2 3 4 5
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What stops a recursive CTE?
- COMMIT
- ORDER BY
- LIMIT
- The WHERE condition Correct answer
Explanation: A WHERE condition stops a recursive CTE.
2 Summary - Recursive CTE: match the terms
| Term | Meaning |
|---|---|
recursive |
recursive |
anchor |
base |
UNION ALL |
combine |
WHERE |
stop |