What Is a Common Table Expression?

A CTE is a named SELECT statement introduced by a WITH clause and defined for the rest of the statement. It is not stored anywhere; it is a way of naming an intermediate result so the main query can refer to it.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO orders (id, customer_id, amount, status) VALUES
  (1, 1, 120.00, 'shipped'),
  (2, 1, 340.50, 'pending'),
  (3, 2,  75.25, 'shipped'),
  (4, 2,  15.00, 'cancelled');

WITH shipped AS (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'shipped'
),
totals AS (
  SELECT customer_id, SUM(amount) AS shipped_total
  FROM shipped
  GROUP BY customer_id
)
SELECT customer_id, shipped_total
FROM totals
WHERE shipped_total > 100
ORDER BY customer_id;

Two rows come back: customer 1 with 460.50 and customer 2 with 75.25.

The structural differences from a subquery are the name and the scope. A CTE is given a name and can be referenced by name, which means several CTEs can build on each other — here shipped feeds totals, and both are readable in the final query. A subquery is an expression nested inside a clause, so it has no name of its own and cannot be referenced twice; if you need the same intermediate result in two places you either repeat it or use a CTE.

The WITH clause can appear at the start of a SELECT, and also at the start of an INSERT, UPDATE, DELETE and MERGE, where it supplies the rows the statement operates on. It cannot appear inside a subquery in the SQL standard, although SQLite and PostgreSQL both allow a nested WITH.

Two things to know about the name. It must be given, and it must be unique within the statement — you cannot have two CTEs with the same name, and you cannot reuse a CTE name for a table in the FROM clause. And a CTE may be forward-referenced in some engines, but relying on that ordering is a portability risk; write them in dependency order.

It depends on the engine and on how many times you reference the CTE. There are three behaviours, and it is worth knowing which one you are getting.

The first is that a CTE is inlined — treated as if the query text had been pasted into the main query. This is what PostgreSQL does by default, and what SQLite does for non-recursive CTEs. The name disappears and the planner sees the raw query. In that case the CTE changes nothing about the plan; it is purely a readability device.

The second is materialisation: the CTE is evaluated once into a temporary structure and the main query reads from it. This is what MySQL does by default, and what SQLite does for a recursive CTE. It helps when the CTE is referenced several times — computing it once instead of three times — and it hurts when the CTE is referenced once, because you have paid for building the temporary structure that the planner could have folded into the main query.

The third is that some optimizers will refuse to push a predicate or a join condition into a CTE, treating it as an opaque barrier. When that happens, a filter that could have reduced the rows scanned ends up applied after the CTE has already produced them, and the plan gets slower. That is a real effect, and it is why a CTE is not automatically the answer to a performance problem.

The practical guidance. Use a CTE when the query has genuinely distinct stages you want to name, or when you reference the same intermediate result more than once. Use a derived table when you need a single inline stage that the planner should be free to push predicates into. And when a query is slow, look at the plan rather than assuming the CTE is the cause — on PostgreSQL, MATERIALIZED and NOT MATERIALIZED on the CTE let you control the choice explicitly and even compare the two.