A recursive CTE refers to itself, which lets one query walk a tree of any depth, like an org chart:
WITH RECURSIVE team AS (
SELECT employee_id, first_name, 1 AS level
FROM employees WHERE employee_id = 2 -- start: the anchor
UNION ALL
SELECT e.employee_id, e.first_name, t.level + 1
FROM employees AS e
JOIN team AS t ON e.manager_id = t.employee_id -- step: one level down
)
SELECT * FROM team;
The anchor runs once. The recursive part then runs again and again, each time on the rows the previous round produced, until a round produces nothing. Every recursive CTE needs something that eventually stops it: here, people with no reports. When you count upward, add a WHERE limit, or a mistake in the data (a loop) runs forever.
PostgreSQL, MySQL 8, SQLite, Snowflake and DuckDB write WITH RECURSIVE; SQL Server and Oracle accept the same shape without the word RECURSIVE.