SQL Common Table Expressions (WITH)
Name intermediate results with WITH so a complex query reads as clear, ordered steps.
A common table expression (CTE) is a named query defined in aWITH clause that you can then select from like a table. CTEs turn a deeply nested query into a readable sequence of named steps.
with order_revenue as (
select o.customer_id,
sum(oi.quantity * oi.unit_price) as revenue
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by o.customer_id
)
select customer_id, revenue
from order_revenue
order by revenue desc, customer_idWhy use one
A CTE and a subquery in the FROM clause compute the same thing. The CTE wins when the step deserves a name, when you reference it more than once, or when you want to read the query top to bottom instead of inside out.
Chaining multiple CTEs
List several CTEs separated by commas. Each one can build on the CTEs declared before it, so the query reads as a pipeline.
with order_revenue as (
select o.customer_id,
sum(oi.quantity * oi.unit_price) as revenue
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by o.customer_id
),
ranked as (
select customer_id,
revenue,
rank() over (order by revenue desc) as revenue_rank
from order_revenue
)
select customer_id, revenue, revenue_rank
from ranked
where revenue_rank <= 10
order by revenue_rank, customer_idA CTE can only reference CTEs defined above it in the sameWITH clause. List them in dependency order, first step first.
CTE vs subquery vs view
| Tool | Reach for it when |
|---|---|
WITH / CTE | A step needs a name, is reused, or the query reads better as stages. |
| Subquery | The intermediate result is small, used once, and inline is clear enough. |
| View | The named query should be reusable across many different queries. |
Recursive CTEs
WITH RECURSIVE lets a CTE reference itself, which is how you walk hierarchies (an org chart, a category tree) or generate sequences. A base query seeds the result; the recursive part repeats until it adds no new rows.
with recursive counter as (
select 1 as n
union all
select n + 1
from counter
where n < 5
)
select n
from counterA CTE is a name, not a stored table. It lives only for the single statement that defines it. Some databases treat a CTE as an optimization fence (materializing it); DuckDB, which powers SQLShed, generally inlines CTEs, so referencing one twice does not force it to run twice.