CTE: multi-step queries with WITH

Real queries tend to grow: three JOINs, a GROUP BY, a subquery, another subquery… and they become unreadable. CTEs ("Common Table Expressions", the WITH clause) let you give a name to an intermediate step and use it as if it were a table in the main query.

Basic syntax

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

A CTE is valid only for the query that follows it: it does not survive the execution.

Example

Find the 3 customers who have spent the most (sum over delivered orders):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

Read in English: "first compute the spend per customer; then join with customers and take the top 3". Without a CTE you'd need an inline subquery in the FROM (legal, but noisier).

Multiple CTEs in cascade

You can define multiple CTEs separated by commas, and each one can refer to the previous ones:

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average because average always has a single row: the Cartesian product just appends total_average to every row of the result.

CTE vs subquery: when to use which

CTEs shine when:

• the same subquery is used two or more times in the main query;
• you want to make the structure explicit in named steps (self-documenting);
• they are the prerequisite for recursive CTEs (WITH RECURSIVE, the next lesson in this module).

An inline subquery is more concise for a step used only once.

Try it

Review exercise