# CTEs and Recursive Queries — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/c-recursive

> Walk trees and graphs.

## Anchor plus recursive member

A **CTE** (`WITH name AS (...)`) names an intermediate result, making long queries readable step by step; PostgreSQL inlines simple CTEs into the main query, so they are usually not a performance barrier. A **recursive CTE** has an anchor query (the roots) and a recursive part joined to the previous results, repeated until no new rows appear. It walks org charts, category trees and bill-of-materials. Track depth and a path, and guard against cycles in graph data.

## Compose queries from parts

CTEs name steps, recursive CTEs walk hierarchies, LATERAL runs a subquery per row, and set operations combine results.

![Three ideas: recursive CTEs, LATERAL, set operations.](assets/figures/sql-deep-dive/section-5-map.svg) — Figure 5.1 — Recursive CTEs, LATERAL and set operations.

## Walking the management chain, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Asha is the root; each level adds one to depth and extends the path string.

```sql
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS depth, name::text AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1, c.path || ' > ' || e.name
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT depth, path FROM chain ORDER BY path;
```

Output:

```
 depth |        path        
-------+--------------------
     0 | Asha
     1 | Asha > Meera
     1 | Asha > Nina
     1 | Asha > Omar
     1 | Asha > Ravi
     2 | Asha > Ravi > John
     1 | Asha > Sara
     2 | Asha > Sara > Ken
     2 | Asha > Sara > Li
(9 rows)
```

## Add a depth limit for untrusted data

A WHERE depth < 20 condition stops runaway recursion if the data contains a cycle.

**Quiz:** What are the two parts of a recursive CTE?

- [ ] A SELECT and an ORDER BY
- [x] An anchor query and a recursive query joined to the previous results
- [ ] Two tables of equal size
- [ ] A trigger and a function

*Answer:* An anchor query and a recursive query joined to the previous results. Combined with UNION ALL.