cte

Recursive CTE: Building a Number Series

Use a recursive CTE to generate a sequence of integers from 1 to 10.

Overview

A recursive CTE has two parts: an anchor (UNION ALL base case) and a recursive step that references the CTE itself. It's the tool for hierarchical data, graph traversal, and generating sequences. This example generates the numbers 1 through 10 — the simplest possible recursion.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

SELECT 1 — the anchor: the first row, n = 1.
UNION ALL SELECT n + 1 FROM counter WHERE n < 10 — the recursive step, which keeps adding 1 until n reaches 10.
The final SELECT n FROM counter returns the accumulated sequence.

Always have a terminator

The WHERE n < 10 guard is what stops recursion. Without a bound you'll hit a maximum recursion depth error.

Variations

Generate the first 5 hire dates' anniversary sequence (dates):

Employees & Departments

Loading database engine...

Common Mistakes

Infinite recursion. Forgetting the WHERE bound in the recursive step blows past the engine's recursion limit.
Using UNION instead of UNION ALL. UNION de-duplicates each iteration, which is slower and can wrongly terminate the series.
Missing RECURSIVE keyword. DuckDB/Postgres require WITH RECURSIVE for self-referencing CTEs.