A subquery and a Common Table Expression can often return the exact same rows. They are not interchangeable in practice. A subquery nests inside another clause and buries its logic in the middle of the statement it belongs to; a CTE is declared up front, given a name, and referenced like a table. The difference is not performance in most engines — it is whether the next person reading the query can follow what it does.
This tutorial walks through CTEs as a sequence of concrete steps: what the syntax looks like, how to build one from scratch, how to chain several together, and when reaching for a CTE makes a query worse rather than better.
Step 1: Understand What a CTE Is
A Common Table Expression is a named temporary result set that exists only for the duration of a single query. You define it with the WITH keyword, give it a name, write a normal SELECT inside parentheses, and then reference that name later in the same statement as if it were a table.
WITH monthly_totals AS (
SELECT
salesperson,
DATE_TRUNC('month', sale_date) AS sale_month,
SUM(amount) AS month_total
FROM sales
GROUP BY salesperson, DATE_TRUNC('month', sale_date)
)
SELECT *
FROM monthly_totals
WHERE month_total > 10000;
Nothing above is possible that couldn’t be written with a subquery instead. The value of the CTE here is that the aggregation step has a name — monthly_totals — and that name describes the intent of the block. When the query grows, that name is what keeps it readable.
A CTE’s scope is the statement it belongs to. Once that statement finishes, the CTE disappears. You cannot reference monthly_totals from a separate query later in the same session.
Step 2: Learn the Exact Syntax
The shape is fixed and worth committing to memory, because getting it wrong produces errors that can be confusing at first.
WITH cte_name AS (
-- any SELECT statement
)
SELECT ...
FROM cte_name;
Three rules cover most syntax mistakes:
- The
WITHkeyword comes before the mainSELECT, not afterFROM. - The CTE body goes inside parentheses, right after the name and
AS. - A trailing semicolon goes only at the very end of the whole statement.
If your database supports multiple CTEs, separate them with commas and keep a single WITH at the front:
WITH
first_cte AS (SELECT ...),
second_cte AS (SELECT ...)
SELECT ...
FROM first_cte
JOIN second_cte ON ...;
Some dialects (notably PostgreSQL and SQL Server) allow a comma after the WITH keyword to align the CTE names visually, but not all engines accept it, so write WITH name AS without that comma unless you know your engine does.
Step 3: Build a Query Step by Step
The clearest way to internalize CTEs is to start from a query that already works, then refactor it. Suppose you have a sales table with columns salesperson, region, sale_date, and amount.
Start with a single query that finds each region’s top-selling salesperson by total sales.
SELECT
region,
salesperson,
SUM(amount) AS total_sales
FROM sales
GROUP BY region, salesperson
ORDER BY region, total_sales DESC;
That gives every salesperson per region with their total, but not the single top one. To isolate the top row per region, you need a ranking applied after the aggregation. That’s two logical stages, and it’s the perfect shape for a CTE.
First stage — compute totals per salesperson per region:
WITH regional_totals AS (
SELECT
region,
salesperson,
SUM(amount) AS total_sales
FROM sales
GROUP BY region, salesperson
)
SELECT *
FROM regional_totals;
Run this and confirm the rows look right before adding the next stage. That verify-each-step habit is one of the practical advantages of a CTE: you can comment out the second half and check the first half on its own.
Second stage — rank within each region and keep the top row:
WITH regional_totals AS (
SELECT
region,
salesperson,
SUM(amount) AS total_sales
FROM sales
GROUP BY region, salesperson
),
ranked AS (
SELECT
region,
salesperson,
total_sales,
ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY total_sales DESC
) AS rn
FROM regional_totals
)
SELECT
region,
salesperson,
total_sales
FROM ranked
WHERE rn = 1;
Read top to bottom, this query tells a story: build totals, rank them, filter to the winner. The equivalent subquery version requires nesting the aggregation inside the window function inside an outer filter, and the reading order runs from the outside in — a direction most people process more slowly.
Step 4: Chain Multiple CTEs in Order
Once you have two CTEs working, adding a third or fourth follows the same rhythm. Each CTE can reference any CTE defined above it, but never one defined below it. This ordering constraint is worth respecting because it forces a natural data-flow shape into the query.
WITH
-- Stage 1: filter to the date range of interest
recent_sales AS (
SELECT *
FROM sales
WHERE sale_date >= DATE '2026-01-01'
),
-- Stage 2: aggregate by salesperson
per_person AS (
SELECT
salesperson,
SUM(amount) AS total_sales
FROM recent_sales
GROUP BY salesperson
),
-- Stage 3: join back for contact info
with_contact AS (
SELECT
p.salesperson,
p.total_sales,
c.email
FROM per_person p
JOIN salespeople c ON c.name = p.salesperson
)
SELECT *
FROM with_contact
ORDER BY total_sales DESC;
The three stages line up with three distinct ideas: narrow the data, summarize it, enrich it. If someone reads only the CTE names, they should still understand roughly what the query does. When CTE names stop conveying that, it’s usually a sign that the query should be simplified or the names rewritten.
Step 5: Verify Each Stage Independently
CTEs make debugging a long query easier than subqueries because each stage is addressable by name. The pattern that works reliably:
- Write the first CTE and a
SELECT * FROM that_cte;after it. - Run the query. Check row counts, NULLs, and value ranges.
- Add the next CTE and change the final
SELECTto reference the new one. - Repeat until the final query is in place.
For the example from Step 3, verifying the intermediate stages looks like this:
-- Verify regional_totals has one row per region+salesperson pair
WITH regional_totals AS (
SELECT region, salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY region, salesperson
)
SELECT region, COUNT(*) AS salesperson_count
FROM regional_totals
GROUP BY region;
If that returns regions with an unexpectedly high or low count, something is wrong with the upstream aggregation and there’s no point continuing until it’s fixed. Chasing a bug through five chained CTEs is harder than catching it at stage one.
Step 6: Know When NOT to Use a CTE
CTEs are a readability tool first. They are not automatically faster, and in some engines they are slower than the equivalent subquery or derived table.
A few situations where a CTE is the wrong choice:
- One simple filter or lookup. If the “CTE” is a single-column subquery used once, inline it.
WHERE region IN (SELECT region FROM regions WHERE active)is clearer than wrapping the inner query inWITH. - Correlated subqueries in the SELECT list. A CTE is defined once and referenced by name; it cannot vary per row. If you need a value computed differently for each outer row, a correlated subquery or a
JOIN/LATERALis the right tool. - Massive intermediate result sets in older engines. In some versions of PostgreSQL (before 12) and MySQL (before 8), a CTE was always materialized, meaning the intermediate result was written to a temporary location before the outer query read it. Rewriting as a subquery could allow the optimizer to fuse the stages and avoid that cost. Modern versions of both engines can inline non-recursive CTEs that are referenced once, but the behavior is version- and query-dependent.
- Hot loops or high-concurrency paths. If the CTE materializes a large intermediate result every time the query runs, and the query runs many times per second, the extra I/O can dominate. Measure before assuming; do not optimize on a hunch.
A good rule: reach for a CTE when the query has at least two distinct logical stages and the goal is human-readable structure. Reach for a subquery when the inner query is trivial or used exactly once.
Step 7: Recognize Where CTEs Help Most
Three recurring patterns show up again and again where a CTE saves the most effort.
Iterative refinement. When the final query can only be written after an intermediate result exists — like the ranking example above — a CTE names that intermediate result instead of nesting it.
Reuse within a single statement. If the same derived table needs to appear in two places (say, joined against once for the “before” numbers and once for the “after”), a CTE written once and referenced twice avoids duplicating the logic. In most modern engines the CTE is still evaluated once, so this is a readability win without a cost.
Recursive queries. A recursive CTE can reference itself, which is how hierarchical data (org charts, category trees, bill-of-materials) is walked in SQL. The syntax adds the RECURSIVE keyword and a UNION ALL joining a base case to a self-referencing step:
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.level + 1
FROM employees e
JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart ORDER BY level, name;
Recursive CTEs are the one case where a CTE is often not just a stylistic preference but the only practical way to express the query. A subtle failure mode: a cycle in your data (A reports to B reports to A) will loop forever unless the query includes a depth limit or a CYCLE/path-tracking clause. Always add a guard when the underlying data might contain loops.
Step 8: Watch for Common Pitfalls
A short list of traps that come up repeatedly when writing CTEs:
WITHmust be at the top of the statement. You cannot interleave a CTE betweenSELECTandFROM. Every CTE in a statement lives in the single leadingWITHblock.- Column names come from the inner
SELECT. Reusing the same alias twice in different CTEs is fine, but referencing a column that the CTE did not project will fail. Alias carefully at each stage. - Scoping is downward. A CTE cannot reference one defined after it, even if they end up in different branches of the final query.
- Order of clauses matters.
ORDER BYinside a non-recursive CTE is usually pointless because the outer query’s ordering is what the user sees. SaveORDER BYfor the finalSELECT. WITHis not a view. It doesn’t persist, cannot be indexed, and cannot be shared across statements. If you need persistence, create a view or a materialized view instead.
Putting It Together
A CTE is not a special SQL feature that unlocks new capabilities — it is a way to structure a query so its stages are visible and named. The steps, in order: understand what a CTE is, get the syntax right, build the query one stage at a time, chain more stages as needed, verify each intermediate result, then decide whether the CTE is earning its keep or just adding ceremony.
The most common question to ask yourself before writing WITH is whether the query has more than one logical stage. If it does, a CTE almost always reads better than the nested alternative. If it doesn’t, a plain subquery is usually the simpler answer.
What query are you trying to restructure, and how many distinct stages does it involve? Describe the shape of your data and the result you need, and the CTE layout can be worked out from there.