Advanced · Lesson 8 of 18

Recursive CTEs

WITH RECURSIVE lets a CTE reference itself, enabling loops in SQL.

Generate numbers 1 through 10:

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

How it works:

  1. Base case: SELECT 1 AS n (the starting point)
  2. Recursive step: SELECT n + 1 FROM nums WHERE n < 10
  3. Stops when the WHERE condition is false

Real-world uses:

  • Traversing org chart hierarchies
  • Bill of materials (parts within parts)
  • Generating date series
Check your understanding

What are recursive CTEs used for?

Examples run on the employees sample database. Open any of them in the playground to experiment.

Practice it on a real question

All SELECT & filtering questions

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app