Common table expressions (CTEs)
Name a subquery up front with WITH, so complex queries read top to bottom.
- Define a named result set with WITH
- Build a readable query in stages
- See why CTEs beat deeply nested subqueries for clarity
A common table expression (CTE) names a query with WITH, then uses that name
like a table in the main query. It's the same power as a subquery, but it reads
top to bottom instead of inside out — a big win for clarity (the same
"decomposition" instinct from the fundamentals track).
WITH … AS
Compute each order's total quantity once, name it order_totals, then query it:
The CTE is defined first; the final SELECT reads from it as if it were a table.
Building in stages
You can define multiple CTEs, each building on the last, separated by commas — turning a gnarly nested query into a readable pipeline:
WITH order_totals AS (
SELECT order_id, SUM(quantity) AS items FROM order_items GROUP BY order_id
),
big_orders AS (
SELECT order_id FROM order_totals WHERE items > 3
)
SELECT * FROM big_orders;Each step has a name and one job — far easier to read (and debug) than the same logic nested three subqueries deep.
CTEs can also be recursive (for hierarchies like an org chart) with
WITH RECURSIVE — a powerful advanced tool, supported by SQLite, PostgreSQL,
and SQL Server.
Where to go next
CTEs and joins combine tables. Next: set operations — stacking the results of two queries with UNION.