sqlstreak

SQL CTEs: the WITH clause

A common table expression (CTE) names an intermediate result with WITH name AS (…) so you can build a big query in readable steps.

WITH syntax

Define one or more named queries before your main SELECT, then use them like tables.

WITH order_totals AS (
  SELECT o.id, SUM(oi.quantity * m.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN menu_items m ON m.id = oi.item_id
  WHERE o.status = 'delivered'
  GROUP BY o.id
)
SELECT AVG(total) AS avg_order, MAX(total) AS biggest_order
FROM order_totals;

Chaining steps

Separate several CTEs with commas; each can use the ones defined before it. Reading top to bottom matches how you'd explain the logic out loud.

WITH spending AS (
  SELECT o.customer_id, SUM(oi.quantity * m.price) AS spent
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN menu_items m ON m.id = oi.item_id
  WHERE o.status = 'delivered'
  GROUP BY o.customer_id
),
big_spenders AS (
  SELECT customer_id, spent FROM spending WHERE spent > 50
)
SELECT c.name, b.spent
FROM big_spenders b
JOIN customers c ON c.id = b.customer_id
ORDER BY b.spent DESC;

CTE or subquery?

Anything a CTE does, a subquery in FROM can do too, and modern PostgreSQL and SQLite plan them similarly. Choose a CTE when it makes the query easier to read or when you reuse the same step twice.

Common mistakes

  • Forgetting the comma between CTEs, or adding one before the final SELECT.

  • Assuming a CTE is always cached or faster. It's mainly a readability tool.

Questions

What is a recursive CTE?+

WITH RECURSIVE lets a CTE refer to itself, for hierarchies like org charts or category trees. Both PostgreSQL and SQLite support it.

Can I use a CTE with UPDATE or DELETE?+

Yes, in both PostgreSQL and SQLite a WITH clause can precede INSERT, UPDATE and DELETE.

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now