What you'll learn

  1. Quick answer
  2. The basic form
  3. Chaining steps is where it pays off
  4. Recursive CTEs
  5. The thing that will bite you
  6. Practical notes
  7. FAQ

Quick Answer

A CTE is a named temporary result defined with WITH at the start of a query. It makes complex queries readable by naming intermediate steps, and recursive CTEs can walk hierarchies such as org charts.

The basic form

WITH region_totals AS (
  SELECT region, SUM(amount) AS total
  FROM sales
  GROUP BY region
)
SELECT region, total
FROM region_totals
WHERE total > 1000;

-- [('North', 1500), ('South', 1300)]

WITH name AS (query) defines a result set you can then select from as if it were a table. It exists only for the duration of the statement.

Compare against the subquery version:

SELECT region, total FROM (
  SELECT region, SUM(amount) AS total FROM sales GROUP BY region
) t WHERE total > 1000;

Identical result. But you read the subquery version inside out, and the intermediate step has a meaningless name. With one level that is fine. With three it is where SQL gets its reputation.

Chaining steps is where it pays off

Several CTEs can be defined in one WITH, separated by commas, and later ones can reference earlier ones:

WITH active AS (
  SELECT * FROM student WHERE status = 'active'
),
scored AS (
  SELECT stream, AVG(marks) AS avg_marks
  FROM active
  GROUP BY stream
),
ranked AS (
  SELECT stream, avg_marks,
         RANK() OVER (ORDER BY avg_marks DESC) AS rnk
  FROM scored
)
SELECT * FROM ranked WHERE rnk <= 3;

Four named steps, read top to bottom, each doing one thing. The nested-subquery equivalent is the same logic wrapped three deep, and changing the middle step means carefully counting brackets.

This is also the standard way to filter on a window function, which cannot appear in WHERE — compute the rank in a CTE, filter in the outer query.

Recursive CTEs

A recursive CTE refers to itself, which lets one query walk a hierarchy of unknown depth.

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 5
)
SELECT x FROM n;
-- [1, 2, 3, 4, 5]

Two parts joined by UNION ALL: the anchor (SELECT 1) runs once, and the recursive member runs repeatedly against the previous result until it returns nothing.

The real use is hierarchical data:

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employee WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.level + 1
  FROM employee e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;

That returns an entire reporting tree of arbitrary depth in one query. Without recursion you would need a loop in application code issuing one query per level.

The same shape handles category trees, folder structures, bill-of-materials explosions and comment threads.

The thing that will bite you

A recursive CTE with no terminating condition runs until the database stops it. Remove WHERE x < 5 from the counting example and it generates rows indefinitely.

Worse, hierarchical data with a cycle — an employee who is transitively their own manager, usually from a data entry error — loops forever even with a sensible-looking query.

Two defences. Track depth with a level column and add WHERE level < 100, which bounds the work regardless. And accumulate the visited path to detect revisits, if your database supports array or string aggregation.

Databases have configurable recursion limits, but relying on the limit means relying on an error rather than on correct logic.

Practical notes

  • Support is broad. PostgreSQL, SQL Server, Oracle, SQLite and MySQL 8+ all support CTEs. MySQL 5.7 does not, which is the usual reason older code avoids them.
  • Performance is usually the same as the equivalent subquery — most optimisers treat them identically. PostgreSQL historically materialised CTEs as an optimisation fence; since version 12 it inlines them unless you write MATERIALIZED.
  • They are not stored. Unlike a view, a CTE exists only within its statement. If several queries need the same logic, a view is the right tool.
  • Naming is the point. WITH t1 AS (...), t2 AS (...) throws away the readability benefit. Name the steps for what they contain.

In interviews, using a CTE for a multi-step problem reads as more experienced than deeply nested subqueries, and it makes your reasoning visible to the interviewer as you build it.

Frequently Asked Questions

What is a CTE in SQL? A common table expression — a named temporary result defined with WITH at the start of a statement, which you can then select from as if it were a table.
Is a CTE faster than a subquery? Usually identical, since optimisers generally treat them the same way. The benefit is readability, not speed.
What is the difference between a CTE and a view? A CTE exists only inside the statement that defines it. A view is stored in the database and reusable across many queries.
What is a recursive CTE used for? Walking hierarchies of unknown depth — org charts, category trees, folder structures and comment threads — in a single query rather than looping in application code.
Why does my recursive CTE never finish? Either the terminating condition is missing, or the data contains a cycle. Add a depth counter with an upper bound so the query is bounded regardless of the data.