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.