Common table expressions

A common table expression gives a subquery a name for the duration of one statement:

WITH totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM totals WHERE total > 100;

It changes nothing a derived table could not do. What it changes is the reading order: each step is written once, named, and read top to bottom, instead of nested inside out. A later CTE may refer to an earlier one, and the main query may refer to a CTE more than once.

Steps with names

The "above the average customer" question from the subqueries lesson, as two named steps:

1CREATE TABLE orders (2  id          int PRIMARY KEY,3  customer_id int NOT NULL,4  amount      int NOT NULL5);6INSERT INTO orders VALUES7  (1, 1, 120), (2, 1, 80), (3, 2, 300), (4, 3, 40), (5, 3, 20), (6, 4, 250);

1WITH totals AS (2  SELECT customer_id, SUM(amount) AS total3  FROM orders4  GROUP BY customer_id5),6average AS (7  SELECT AVG(total) AS avg_total8  FROM totals9)10SELECT t.customer_id, t.total11FROM totals t12CROSS JOIN average a13WHERE t.total > a.avg_total14ORDER BY t.customer_id;

totals is used twice — once to compute the average, once to filter — and written once. CROSS JOIN pairs every row on one side with every row on the other; average has exactly one row, so it simply puts the average next to each total. A CTE exists only inside its statement; it is not a temporary table and leaves nothing behind.

Recursive CTEs

WITH RECURSIVE (SQL Server writes plain WITH) lets a CTE refer to itself, which is how SQL walks a tree stored as parent_id pointers. It has two parts joined by UNION ALL:

1. the anchor — a normal query that produces the starting rows;
2. the recursive member — a query that joins the CTE to the table to produce the next level.

The database runs the anchor, then runs the recursive member against the rows produced in the previous step, again and again, and stops when a step produces no new rows.

1CREATE TABLE employees (2  id         int PRIMARY KEY,3  name       text NOT NULL,4  manager_id int5);6INSERT INTO employees VALUES7  (1, 'Nino', NULL),8  (2, 'Ana', 1),9  (3, 'Luka', 1),10  (4, 'Mari', 2),11  (5, 'Giorgi', 4),12  (6, 'Tamar', 3);

1WITH RECURSIVE chain AS (2  SELECT id, name, manager_id, 0 AS depth3  FROM employees4  WHERE manager_id IS NULL5  UNION ALL6  SELECT e.id, e.name, e.manager_id, c.depth + 17  FROM employees e8  JOIN chain c ON e.manager_id = c.id9)10SELECT id, name, depth11FROM chain12ORDER BY depth, id;

The depth column is carried along and incremented at each step, which is the usual way to know how far from the start a row is. The anchor here picks the root (manager_id IS NULL); start it from any id and you get that person's whole subtree instead.

When recursion does not stop

The loop ends only when a step adds nothing. If the data has a cycle — A manages B, B manages A — UNION ALL keeps producing rows until the query times out (here, PostgreSQL's "canceling statement due to statement timeout"). Defences: a depth limit in the recursive member (WHERE c.depth < 20), or UNION instead of UNION ALL, which drops a row already produced, provided the rows repeat exactly (a growing depth column means they never do).

Common mistakes

• Joining in the wrong direction. JOIN chain c ON e.id = c.manager_id walks up to the managers instead of down to the reports. Read the condition as "the new row's parent is a row I already have".
• Assuming a CTE is computed once and cached. Engines differ: PostgreSQL 12 and later may fold a CTE into the main query like a subquery, and SQL Server always does. Use one for readability, not as a performance tool.
• No guard against cycles. Hierarchies edited by people eventually contain one.

What interviewers ask

• What is a CTE and why use one? A named subquery scoped to one statement; it makes multi-step queries readable and lets a step be reused within the statement.