Understanding SQL Common Table Expressions

A Common Table Expression — or CTE — is a named temporary result set that exists only for the duration of a single query. It lets you take a complicated piece of SQL, break it into clearly labelled steps, and reference each step by name, turning a tangle of nested logic into something that reads almost like a recipe.

What Is a CTE?

Imagine you are solving a multi-step arithmetic problem on paper. You rarely do it all in your head at once — you jot an intermediate figure on a scratch pad, give it a quick label, and reuse that figure in the next step. A Common Table Expression is exactly that scratch pad for SQL. You compute a sub-result, give it a name, and refer to it by that name in the rest of your query.

CTEs are also called WITH queries, because they are introduced with the WITH keyword. They were added to standard SQL in SQL:1999 and have since become a fixture of virtually every major relational database. The defining property is in the definition itself: a CTE is temporary. It is not a table you create and keep — it lives only for the duration of the single statement that defines it, and then it vanishes.

The PostgreSQL documentation captures the mental model precisely: "WITH provides a way to write auxiliary statements for use in a larger query. These statements, often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query."

The Sample Dataset

Every example in this article uses two small tables. You can verify each result by hand against the rows below.

customers

orders

Two small wrinkles are deliberate. Diego (customer 4) has placed no orders at all, and order 105 references customer_id = 5 — a customer who does not exist in the customers table. These edge cases will help illustrate how a CTE behaves when joined to other tables.

Basic CTE Syntax

The skeleton of a CTE is simple:

You write WITH, give the expression a name, follow it with AS and a parenthesised query, and then write a main query that uses that name as though it were a table. Let us put it to work — find every customer whose orders total more than 100:

The CTE gave us a clean, named place to compute the per-customer sums before doing anything else with them. Carla's 27.75 is excluded by the WHERE filter, and customer 5 disappears because no matching customer exists to join to.

CTEs vs Subqueries

Everything a non-recursive CTE does can also be done with a subquery. The difference is largely about readability and reuse. Here is the same logic written as a subquery instead:

For a query this short, the two are roughly equal. But three practical differences are worth keeping in mind:

• Subqueries get harder to read as they nest. When a derived table contains another derived table inside it, you must read from the inside out. CTEs let you read top to bottom.
• CTEs name each intermediate step. A name like customer_totals documents intent in a way that an anonymous ( SELECT ... ) t never does.
• A CTE can be referenced multiple times in the same query; a subquery cannot. If you need the same intermediate result in two places, a subquery forces you to write it twice.

Multiple CTEs

One of the most powerful features of the WITH clause is that you can define several CTEs at once, separated by commas, and each one may reference the CTEs defined before it. Only one WITH keyword is used:

Let us chain two CTEs to answer a question that would be awkward as a single query: which customers placed more orders than the average customer?

The query reads as three clean steps, each with a name describing what it produces.

Referencing a CTE Multiple Times

Because a CTE has a name, the main query can use it as many times as needed. Consider finding every customer who spent more than the average spend across all customers. We need the per-customer totals twice: once to list each customer, and once inside an aggregate to compute the overall average.

The name customer_totals appears twice — once in the outer FROM and again inside the scalar subquery in the WHERE clause. With a derived table you would have to repeat the entire SUM ... GROUP BY block in both places.

CTEs Are Not Just for SELECT

A common misconception is that the WITH clause only works in front of a SELECT. In most modern engines a CTE can also feed an INSERT, UPDATE, or DELETE statement.

This capability is available in PostgreSQL, MySQL 8 and later, and SQL Server. It lets you keep the readable, step-by-step structure of a CTE even when the goal is to modify data rather than read it.

Common Mistakes and Good Practices

Don't overuse CTEs for trivial subqueries

If a subquery is a single, simple expression, wrapping it in a CTE can add ceremony without adding clarity. Reserve CTEs for steps that genuinely benefit from a name, or that you reference more than once.

Name your CTEs meaningfully

The entire point of a CTE is communicative naming. cte1 and cte2 throw that benefit away. Prefer names like customer_totals or monthly_revenue that describe what the result is.

Watch performance with very large intermediate results

A CTE produces a temporary result behind the scenes. If that result contains millions of rows and you only need a handful of them after filtering, you may be doing more work than necessary. If a query becomes noticeably slow, check whether the same logic written as a subquery (or with a filter applied earlier) runs faster.

Remember that CTEs are not persisted

A CTE exists only for the duration of the single statement that defines it. You cannot reference it in a later statement, even within the same session or transaction. If you need a result to survive across multiple statements, that is the job of a temporary table or a view — not a CTE.

A Comprehensive Example

Two chained CTEs combined into one query. The goal: for each customer who has placed orders, show their total spend and order count, and label whether they are above or below the company-wide average spend.

That single statement reads as three labelled stages instead of a knot of nested parentheses. This is the everyday value of CTEs: they let you think — and write — in steps.