Some data isn't flat. An employee has a manager, who has a manager, who has a manager, all the way up to the boss at the top. A comment can be a reply to a reply to a reply. A folder can sit inside a folder inside a folder. This kind of data, where things are connected in layers or "levels," is called hierarchical data. Think of a family tree, or the folders on your computer. Each item points up (or down) to another item of the same kind.
The tricky part: you never know ahead of time how many layers deep the chain goes. One employee might be 2 levels from the top, another might be 6. A recursive CTE is the tool SQL gives you to handle exactly this. A query that keeps digging one layer deeper, automatically, until it runs out of layers.
First, what's a CTE?
CTE stands for Common Table Expression.
Don't let the name intimidate you, all it means is: "give this block of query a name, so I can use it again further down, later in my script." You write it with the keyword WITH.
WITH recent_orders AS (
SELECT * FROM orders WHERE order_date > '2026-01-01'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;
Here, recent_orders isn't a real table sitting in your database. It's a temporary result, given a name, that only exists for this one query.
why not just write a normal query?
A CTE is really just a nicer way of writing something you could squeeze into one messy query anyway; it just keeps things readable by breaking the logic into named, ordered steps instead of one big nested blob.
A recursive CTE takes this one step further: the named result (from the CTE) is allowed to refer to itself. That's the entire trick behind it and it's the only way a query can "loop" over hierarchical data of unknown depth.
Why a normal query can't do this
Picture a simple company organization chart: the CEO, and everyone below them, layer by layer.
This is a hierarchy: everyone connects upward to one person, layer by layer, and you don't know how deep it goes until you get there.
A "normal" query can only join a table to itself a fixed number of times. So if you wanted everyone under the CEO with plain joins, you'd have to write something like: join employees to their manager, then join that result to the next manager up, then join that to the next one and stop wherever you guessed the chart ends. If someone gets hired 7 levels deep next year, your query silently misses them.
A recursive CTE removes the guesswork. You tell it where to start and how to take one step outward and it keeps repeating that one step, on its own, until there's nobody left to find.
The two parts of a recursive CTE
Every recursive CTE is built from two pieces, glued together with UNION ALL (which just means "stack these two result sets on top of each other"):
The anchor -> this runs once, and only once. It's your starting point: "give me the person with no manager" (the CEO), or "give me the top-level category."
The recursive part -> this is the piece that repeats. Each time it runs, it looks for "who connects to the people I just found," and it keeps doing that, again and again, until a round comes back completely empty.
Here's the org chart example written out as SQL. It maps directly onto the two pieces above:
WITH RECURSIVE employee_chain AS (
-- THE ANCHOR: find the one person with no manager (the CEO)
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- THE RECURSIVE PART: find everyone whose manager we just found
SELECT e.id, e.name, e.manager_id, ec.level + 1
FROM employees e
JOIN employee_chain ec ON e.manager_id = ec.id
)
SELECT * FROM employee_chain;
a note on syntax
WITH RECURSIVE is what Postgres and SQLite expect. MySQL (version 8 and up) and SQL Server are happy with just WITH — no extra word needed. Small detail, but it trips people up.
You don't need to think of this as clever. It's just "keep repeating one step", automated.
Watching it run, step by step
The easiest way to actually understand this is to stop thinking of it as one magic statement, and picture the database running it in rounds:
The database is just doing the "join, join, join" you'd otherwise write by hand, and it's smart enough to know when to stop.
The one thing to be careful of:
If your data has a loop in it by mistake say, an employee who is (accidentally) listed as their own manager's manager, the recursive part will never come back empty, and the query will run forever. Most databases let you set a safety limit: SQL Server has OPTION (MAXRECURSION n), and elsewhere you can add your own cutoff, like WHERE ec.level < 20, inside the recursive part.
Is there such a thing as a "recursive subquery"?
You'll hear this phrase sometimes, but strictly speaking, no, and understanding why makes the whole topic click better.
A subquery is a query with no name, tucked inside another query.
SELECT name
FROM employees
WHERE manager_id = (
SELECT id FROM employees WHERE name = 'Jane'
);
Recursion needs something to call back to by name - and an unnamed query has nothing to point at. A CTE gets a name the second you write it, and that name is exactly what the recursive part calls. No name, no recursion. That's really the entire difference.
What people usually actually mean
Oracle's older way: CONNECT BY
Before recursive CTEs existed in the SQL standard, Oracle already had its own way of walking a hierarchy: CONNECT BY, paired with a built-in "column" called LEVEL. It solves the exact same problem, it's just written as a subquery tacked onto a normal SELECT, instead of a self-referencing WITH block.
SELECT employee_id, name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
CONNECT BY looks like one plain SELECT statement, which is probably why people call it a "recursive subquery" but it's doing the same job as a recursive CTE.
One nice thing Oracle gives you for free here: LEVEL automatically tells you how deep you are in the hierarchy. In a recursive CTE, you have to build that counter yourself, like we did with level + 1 earlier.
CONNECT BY only works in Oracle. Modern Oracle also supports the standard WITH RECURSIVE CTE and most new projects use that instead, since it's the version that works, unchanged, on Postgres, SQL Server, MySQL 8+, and SQLite too.
The one idea to hold onto
A recursive CTE isn't really a special trick. It's a pattern with a name.
Pick a starting point. Define one step that reaches one layer further out. Let the database repeat that step on its own, until nothing new is left to find.
Once that clicks, every hierarchy: org charts, comment threads, category trees, folders inside folders, starts looking like the same problem, just wearing a different table name.
Top comments (2)
The WITH RECURSIVE vs plain WITH syntax gotcha across databases is the kind of detail that costs people some minutes of confused debugging the first time they move a query from Postgres to MySQL. Also a good call including CONNECT BY, most recursive CTE writeups pretend Oracle doesn't exist even though a lot of legacy enterprise systems still lean on it heavily.
Interesting