CTEs, Recursive Queries and Set Operations

In one line

Common table expressions (WITH) name intermediate results for readability; since PostgreSQL 12 they're inlined unless referenced twice or marked MATERIALIZED. Recursive CTEs walk hierarchies and graphs. UNION, UNION ALL, INTERSECT and EXCEPT combine result sets, and UNION ALL is the one to reach for unless you need de-duplication.

0/6 · 0%

Think of it like this

A family tree. To list everyone descended from a grandparent, you start with the grandparent (anchor), find their children, then those children's children, and so on until nobody new appears. That is exactly a recursive CTE.

Key ideas

01
A recursive CTE has an anchor query, UNION ALL, and a recursive part that joins to the CTE itself; execution repeats until the recursive part returns no rows.
02
Guard against cycles in graphs: track the path (ARRAY[id]) and stop when revisiting, or use PostgreSQL 14's CYCLE clause. Also cap depth.
03
UNION removes duplicates (sort or hash of the whole result); UNION ALL just concatenates and is much cheaper. INTERSECT keeps common rows; EXCEPT keeps rows in the first set but not the second.
04
Data-modifying CTEs chain writes atomically: WITH moved AS (DELETE FROM queue ... RETURNING *) INSERT INTO archive SELECT * FROM moved;

Code & diagrams

org-chart.sqlsql

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth, ARRAY[id] AS path
  FROM employee WHERE id = 1                      -- anchor: the CEO
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1, c.path || e.id
  FROM employee e
  JOIN chain c ON e.manager_id = c.id
  WHERE e.id <> ALL (c.path) AND c.depth < 20     -- cycle and depth guard
)
SELECT repeat('  ', depth - 1) || name AS org FROM chain ORDER BY path;
--  org
-- ---------------
--  Asha
--    Bilal
--      Chen
--    Divya

-- move processed jobs to an archive atomically
WITH done AS (
  DELETE FROM job WHERE status = 'done' AND finished_at < now() - interval '7 days'
  RETURNING *
)
INSERT INTO job_archive SELECT * FROM done;

Interview problem

The problem

Category breadcrumbs and subtree counts

Categories form a tree (category(id, parent_id, name)). Build (a) the breadcrumb for category 123 and (b) the number of products in category 7 including all subcategories. Discuss when to switch to a different tree model.

When it breaks

Recursive CTE on cyclic data

What you see

A bad data edit makes A the parent of B and B the parent of A; the query recurses until it exhausts memory or temp space.

Fix & prevent

Path-based cycle detection or the CYCLE clause, a depth cap, and a constraint or trigger that prevents cycles on write.

Explain it without notes

When is UNION ALL correct and UNION wasteful?

Practice

Using EXCEPT, list customers who bought in January but not February.

Trade-offs

↔
Adjacency lists with recursive CTEs are simple to write to; closure tables and ltree are faster to read but costlier to maintain.

Done when you can

I can write recursive CTEs with cycle guards and choose the right set operation.

Command Palette