Lesson 21 — Recursive CTE

Recursive CTE: SQL that loops

A CTE (Common Table Expression) is a named temporary result written with WITH name AS (...). A recursive CTE refers to itself: an anchor part gives the first row(s), then a part after UNION ALL builds the next row from the previous one, until a WHERE stops it.

WITH RECURSIVE n(x) AS (
  SELECT 1                              -- anchor
  UNION ALL SELECT x + 1 FROM n WHERE x < 3   -- step
) SELECT x FROM n                       -- 1, 2, 3

Why it matters: recursive CTEs generate date ranges and walk hierarchies (org charts, folder trees) directly in the database.