A Common Table Expression, or CTE, is simply a named, temporary result set that you define at the start of a query using the WITH keyword, and then reference later in that same query just like you would reference any regular table. That single sentence is the entire definition, and yet CTEs are frequently introduced through examples complex enough to obscure how genuinely simple this core idea actually is.
This tutorial builds up gradually, starting with the simplest possible CTE before introducing the more advanced patterns, including the specific case — recursive CTEs — where the concept genuinely does become more demanding and deserves dedicated attention on its own.
The Core Idea: Naming a Subquery Result for Reuse and Readability
As covered in our subquery tutorial, a subquery placed in the FROM clause effectively treats a query’s result as a temporary table. A CTE accomplishes something genuinely similar, but with one meaningful difference: you define it once, give it a clear name, and then that name can be referenced anywhere in your main query that follows, rather than needing to write the subquery’s full logic inline at the specific spot where you use it.
This naming and separation is the entire practical benefit of a basic CTE over an equivalent FROM-clause subquery — the underlying database engine often processes them very similarly, but the readability difference for anyone reading the query afterward, including your future self, can be considerable.
The Simplest Case: A Single CTE Used Once
The structure starts with the WITH keyword, followed by your chosen name for the CTE, followed by AS and an opening parenthesis, then a complete SELECT statement defining what that CTE actually contains, followed by a closing parenthesis. Your main query then follows directly after, referencing that CTE’s name in its own FROM clause just as it would reference any genuine table.
Imagine calculating total sales per salesperson, then filtering to only salespeople above a certain threshold. Without a CTE, this commonly requires a FROM-clause subquery: SELECT from an inline SELECT that itself groups and sums sales by salesperson. With a CTE, you write WITH salesperson_totals AS, followed by that same grouping and summing SELECT statement in parentheses, and then your main query simply does SELECT from salesperson_totals WHERE total is above your threshold — referencing the CTE by its clear, descriptive name rather than embedding the entire inner query inline at the point of use.
The actual calculated result is identical either way. The difference is entirely in how clearly the query communicates its own logic to someone reading it.
Why CTEs Improve Readability Over Equivalent Subqueries
This becomes considerably more apparent once queries grow more complex than the single-subquery example above. A query containing several nested FROM-clause subqueries, each embedded inside the next, becomes genuinely difficult to read from the inside out, since you have to mentally track which closing parenthesis belongs to which opening one, often across many lines of indentation.
CTEs let you define each logical step with its own clear name, in a flat, sequential structure, rather than nesting subqueries inside one another. Even when the underlying calculation is exactly as complex, naming each intermediate step clearly, in the order you conceptually think through the problem, makes the query’s actual logic considerably easier to follow for anyone reading it afterward.
Using Multiple CTEs in Sequence
A single WITH clause can define several CTEs one after another, separated by commas, with each subsequent CTE able to reference the ones defined before it. This is where the readability advantage over nested subqueries becomes most apparent.
Imagine you first need to calculate total sales per region, then separately calculate the overall company-wide average of those regional totals, and finally compare each region against that average. Using multiple sequential CTEs, you define WITH regional_totals AS (your first calculation), then a comma, then company_average AS (a calculation referencing regional_totals), then your main query referencing both regional_totals and company_average together in its final comparison logic.
Expressing this same three-step logic using nested FROM-clause subqueries would require embedding one subquery inside another inside another, considerably harder to read than the same logic expressed as three clearly named, sequential CTEs.
Recursive CTEs: When a CTE References Itself
This is the genuinely more advanced pattern, and it deserves to be treated as a distinct skill rather than simply an extension of the basic CTE pattern above. A recursive CTE references itself within its own definition, repeating that self-reference until some stopping condition is met, which makes it specifically suited to hierarchical or sequential data that a standard, non-recursive query structure cannot easily traverse.
A common use case: an employee table where each row has a manager ID referencing another employee in the same table, and you want to find an employee’s entire chain of managers up to the top of the organization, regardless of how many levels deep that chain happens to be.
A recursive CTE handles this by defining an initial “anchor” portion (typically the starting employee), combined with a recursive portion that repeatedly joins back to the same CTE, each iteration moving one level further up the management chain, continuing until no further manager is found. This structure is fundamentally different from the CTEs covered above, since it requires the CTE to reference its own name within its own definition, which a standard CTE does not do at all.
Worth treating as a separate learning step entirely: Recursive CTEs solve a genuinely different category of problem (traversing hierarchical or graph-like relationships of unknown depth) compared to standard CTEs (organizing sequential calculation steps clearly), and conflating the two while learning tends to make both feel more confusing than either actually is in isolation.
CTE vs Subquery vs Temporary Table: When to Use Which
This is a genuinely common point of confusion, since all three can technically express overlapping logic, and beginners are often unsure which to reach for.
As a general guideline: a CTE is typically the clearest choice when you want named, readable, sequential steps within a single query that does not need to persist beyond that one query’s execution. A FROM-clause subquery remains a reasonable choice for a single, simple intermediate calculation where introducing a separate named CTE feels like unnecessary structure for something genuinely simple. A temporary table is worth considering specifically when the intermediate result needs to be reused across multiple separate queries, or when the intermediate calculation is large enough that materializing it physically (rather than recalculating it each time a CTE reference is used) provides a genuine performance benefit on your specific database system.
Neither approach is universally “correct” for every situation, and choosing between them often comes down to which version most clearly expresses your actual intent to someone else reading the query later, alongside performance considerations that can vary across different database systems.
A Progression for Building This Skill
Given how different the basic and recursive CTE patterns genuinely are, attempting to learn both simultaneously is exactly the kind of overwhelm that makes CTEs feel harder than they actually are. I recommend this specific learning sequence:
First, get comfortable rewriting a single FROM-clause subquery you already understand as an equivalent named CTE — this builds the core mental model without introducing any new logical complexity beyond what you already know.
Second, practice chaining two or three sequential CTEs together within a single WITH clause, referencing earlier CTEs from later ones — this introduces the multi-step organizational benefit that makes CTEs genuinely useful beyond simple readability.
Third, and only once the above feels comfortable, treat recursive CTEs as a separate, dedicated topic with hierarchical data specifically, rather than assuming familiarity with basic CTEs automatically prepares you for the self-referencing logic recursive CTEs require.
Rushing toward recursive CTEs before the simpler sequential pattern feels automatic is the most common reason CTEs feel intimidating, rather than simply being another organizational tool in your regular toolkit, the way they eventually become with consistent practice.
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.