CTEs with WITH — Subqueries and CTEs

The problem: nested, unreadable subqueries

Subqueries solve a lot, but when nested they become hard to read: you have to read from the inside out and the same subquery may be repeated.

CTE: a named subquery

A CTE (Common Table Expression) is a subquery to which you give a name with the WITH clause, before the main SELECT. Then you use it as if it were just another table:

WITH category_prices AS (
  SELECT category_id, AVG(price) AS average
  FROM products
  GROUP BY category_id
)
SELECT c.name, cp.average
FROM category_prices cp
JOIN categories c ON c.id = cp.category_id
ORDER BY cp.average DESC;

Advantages:

Readability: the query reads top to bottom, like steps.
Reuse: you can reference the CTE several times in the same SELECT without repeating its code.
Composition: you can define several CTEs separated by commas, and some can build on the previous ones.

WITH
  expensive AS (
    SELECT * FROM products WHERE price > 100
  ),
  in_stock AS (
    SELECT * FROM expensive WHERE stock > 0   -- builds on the previous CTE
  )
SELECT name, price, stock FROM in_stock ORDER BY price DESC;

Recursive CTEs (mention)

With WITH RECURSIVE a CTE can reference itself, which allows traversing hierarchical structures (a tree of parent/child categories, a list of employees and their managers) or generating series of values. It is an advanced topic: for now it is enough to know it exists and that it is the tool for tree- or graph-shaped data, something that ordinary subqueries cannot express.

Practical rule: if you find yourself copy-pasting the same subquery, or nesting three levels, turn it into a CTE with WITH.

Examples

WITH to name an intermediate computation

WITH stats AS (
  SELECT AVG(price) AS average FROM products
)
SELECT name, price
FROM products, stats
WHERE price > stats.average
ORDER BY price DESC;

Several chained CTEs

WITH
  expensive AS (SELECT * FROM products WHERE price > 100),
  in_stock AS (SELECT * FROM expensive WHERE stock > 0)
SELECT name, price FROM in_stock ORDER BY price DESC;