DataslopeCommon Table Expressions
Naming a query with WITH so complex questions read as clear, ordered steps instead of deeply nested subqueries.
Subqueries are powerful, but stacking them inside one another quickly becomes hard to read. A common table expression (CTE) lets you give a subquery a name and write it before the main query, so a complex question reads as a sequence of clear steps.
WITH: name a query, then use it
A CTE is introduced by the keyword WITH. You name a query, and
then refer to that name in the rest of the statement as if it were a
table.
The CTE runs first and produces a temporary, named result; the main query then reads from that name. It is the same idea as a subquery, but pulled out and labelled.
A subquery rewritten as a CTE
Compare the two styles. First a nested subquery, then the same logic as a CTE — notice how the CTE version reads top-to-bottom:
The big_orders CTE defines "orders over 50" once, with a clear
name. The main query then groups and counts them. Anyone reading it
sees the steps in order: find big orders → count them per
customer.
CTEs are about readability
A CTE rarely changes what a query computes — you could usually write the same thing with a subquery. What changes is clarity. Naming each step makes intricate queries far easier to read, debug, and revisit weeks later.
Chaining multiple steps
The real payoff comes when a question has several stages. You can list multiple CTEs separated by commas, and each can build on the ones before it:
Read it as a pipeline: per_customer sums each customer's spending,
big_spenders keeps the large totals, and the final query sorts
them. Each CTE is a labelled stage, and the data flows from one to
the next — much clearer than nesting these as two subqueries.
When to reach for a CTE
Reach for a CTE when a query has multiple logical steps, when a subquery is getting deeply nested, or when you simply want the query to document itself with meaningful names. For a one-line filter, a plain query is fine — use CTEs where they earn their keep.
Check your understanding
What does a common table expression (CTE) let you do?
Permanently create a new table in the database.
Give a query a name with WITH, then reference that name in the rest of the statement.
Run a query without a SELECT.
Automatically index a table.
Compared with a deeply nested subquery, the main advantage of a CTE is usually:
It always produces different results.
It removes the need for joins.
Improved readability, because each step is named and written in order.
It runs without accessing any tables.
How do you write several steps that build on each other in one WITH clause?
You cannot; a statement allows only one CTE.
List multiple CTEs separated by commas, where later ones can reference earlier ones.
Write a separate WITH keyword before each step.
Nest each CTE inside the previous one's parentheses.