Most people learn Common Table Expressions by memorizing a recursive example first — the hardest version of the concept — before ever seeing the simple version that explains what a CTE actually is. That backwards order is probably the single biggest reason CTEs have a reputation for being confusing. Strip away the complexity, and here’s the whole idea in one sentence: a CTE is a named, temporary result set that you define at the start of a query using the WITH keyword, then reference later in that same query just like any regular table.
This tutorial builds up in the opposite direction from how CTEs are typically taught. We’ll start with the simplest possible version, work through the patterns that make CTEs worth using in the first place, and only then arrive at recursive CTEs — the one case where the concept truly does get harder, and where it deserves its own dedicated attention rather than being lumped in with everything else.
The Core Idea: Naming a Subquery Result for Reuse and Readability
Our subquery tutorial covers how a subquery placed in the FROM clause treats a query’s result as a stand-in table. A CTE does something quite similar, with one meaningful twist: instead of writing the subquery’s full logic inline at the exact spot where you need it, you define it once, give it a clear name, and then reference that name anywhere in the rest of your main query.
That act of naming and separating is really the whole practical payoff of a basic CTE compared to an equivalent FROM-clause subquery. The database engine frequently processes the two almost identically under the hood — the difference shows up for the human reading the query afterward, including the version of you who returns to this code in six months.
The Simplest Case: A Single CTE Used Once
Structurally, it starts with WITH, then your chosen name for the CTE, then AS and an opening parenthesis, then a full SELECT statement defining what the CTE contains, then a closing parenthesis. Your main query follows right after, pulling that CTE’s name into its own FROM clause exactly as it would pull in any table.
Picture calculating total sales per salesperson, then keeping only the salespeople above some threshold. Without a CTE, this typically means a FROM-clause subquery: a SELECT pulled from an inline SELECT that itself groups and sums sales by salesperson. With a CTE, you instead write WITH salesperson_totals AS, followed by that same grouping-and-summing SELECT in parentheses — and your main query becomes a simple SELECT from salesperson_totals WHERE total exceeds your threshold, referencing the CTE by a descriptive name instead of embedding the entire inner query at the point of use.
The calculated result comes out identical either way. What changes is how clearly the query explains its own logic to whoever reads it next.
Why CTEs Improve Readability Over Equivalent Subqueries
The gap becomes much harder to ignore once queries grow past that single-subquery example. Several nested FROM-clause subqueries, each tucked inside the next, turn into something you have to read from the inside out — tracking which closing parenthesis belongs to which opening one, often across pages of indentation.
CTEs replace that nesting with a flat, sequential structure: each logical step gets its own clear name, one after another, instead of being buried inside the next layer. The underlying calculation can be exactly as complicated, but naming each intermediate step in the order you naturally think through the problem makes the logic far easier to trace afterward.
Using Multiple CTEs in Sequence
A single WITH clause isn’t limited to one CTE — you can define several, one after another, separated by commas, and each one can reference any CTE defined before it. This is where the readability edge over nested subqueries really shows up.
Say you need to calculate total sales per region, then work out the overall company-wide average of those regional totals, and finally compare each region against that average. With sequential CTEs, you write WITH regional_totals AS (your first calculation), a comma, then company_average AS (a calculation that references regional_totals), and then a main query that pulls from both regional_totals and company_average for the final comparison.
The same three-step logic written as nested FROM-clause subqueries would mean burying one subquery inside another inside another — far messier to follow than three clearly named, sequential CTEs laid out one after the next.
Recursive CTEs: When a CTE References Itself
Here’s where the difficulty jumps, and it’s worth treating this as its own skill rather than a natural extension of what we’ve covered so far. A recursive CTE references itself within its own definition, repeating that self-reference until some stopping condition is reached — which makes it the right tool for hierarchical or sequential data that an ordinary, non-recursive query can’t traverse.
A classic example: an employee table where each row stores a manager ID pointing to another employee in that same table, and the goal is finding an employee’s full chain of managers all the way to the top, no matter how many levels that chain runs.
A recursive CTE solves this with an initial “anchor” portion (typically the starting employee) paired with a recursive portion that keeps joining back to the same CTE, each pass climbing one more level up the management chain, stopping only once no further manager turns up. This is a fundamentally different structure from everything covered above, since it requires the CTE to reference its own name inside its own definition — something a standard CTE never does.
Worth learning as its own separate step: recursive CTEs tackle a different category of problem entirely — traversing hierarchical or graph-like relationships of unknown depth — compared to standard CTEs, which organize sequential calculation steps. Mixing the two together while you’re still learning tends to make both feel murkier than either one is on its own.
CTE vs Subquery vs Temporary Table: When to Use Which
This trips up a lot of beginners, since all three tools can technically express overlapping logic, leaving you unsure which one to reach for.
As a rough guideline: reach for a CTE when you want named, readable, sequential steps inside a single query that doesn’t need to outlive that one execution. A FROM-clause subquery still makes sense for a single, simple intermediate calculation, where a separate named CTE would feel like more structure than the problem warrants. A temporary table earns its keep when the intermediate result needs to be reused across several separate queries, or when that intermediate calculation is large enough that physically materializing it — rather than recalculating it every time a CTE reference gets used — offers a real performance gain on your particular database system.
None of the three wins in every situation. Choosing among them usually comes down to which version communicates your intent most clearly to the next reader, weighed against performance considerations that shift from one database system to another.
A Progression for Building This Skill
Given how far apart the basic and recursive CTE patterns really sit, trying to absorb both at once is a reliable way to make CTEs feel harder than they are. Here’s the sequence I’d recommend instead:
Start by taking a single FROM-clause subquery you already understand and rewriting it as an equivalent named CTE. This builds the core mental model without adding any new logical complexity on top of what you already know.
Next, practice chaining two or three sequential CTEs inside one WITH clause, with later CTEs referencing earlier ones. This is what introduces the multi-step organizational payoff that makes CTEs worth reaching for beyond simple readability.
Only once that feels routine should you treat recursive CTEs as their own dedicated topic, worked through with hierarchical data specifically — rather than assuming comfort with basic CTEs automatically prepares you for the self-referencing logic recursion demands.
Rushing toward recursive CTEs before the sequential pattern becomes second nature is the most common reason they feel intimidating, rather than simply being one more organizational tool that, with steady practice, ends up sitting comfortably in your regular toolkit.
Are you trying to organize a multi-step calculation more clearly, or trying to traverse hierarchical data like an organizational chart? Describe your specific situation and I can help you decide whether a standard or recursive CTE fits your case.