CTEs with WITH

A Common Table Expression, or CTE, lets you name a subquery before the main query begins, and then refer to that name anywhere in the statement as if it were a real table. A CTE is written with the WITH keyword, followed by a name, an AS keyword, and the subquery in parentheses — then the main statement follows.

SQL

WITH customer_totals AS (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, lifetime_value
FROM customer_totals
WHERE lifetime_value > 5000
ORDER BY lifetime_value DESC;

Before: A Derived Table Nested in FROM

A CTE almost always does the exact same work a derived table (a subquery in FROM) already does — it just moves the subquery text out of the middle of the FROM clause and gives it a name up front. Compare the same query written both ways.

SQL

-- BEFORE: nested subquery in FROM (a derived table)
SELECT customer_totals.customer_id, customer_totals.lifetime_value
FROM (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY customer_id
) AS customer_totals
WHERE customer_totals.lifetime_value > 5000
ORDER BY customer_totals.lifetime_value DESC;

SQL

-- AFTER: the same query rewritten as a CTE
WITH customer_totals AS (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, lifetime_value
FROM customer_totals
WHERE lifetime_value > 5000
ORDER BY lifetime_value DESC;

Why This Reads Better

Both queries above run identically and, in most databases, produce the same execution plan — the difference is entirely about how a human reads the SQL. With a CTE, you read top to bottom in the same order you would explain it out loud: "first, compute customer totals — here's how — then select from that, filtered and sorted." With a nested derived table, you have to mentally jump into the middle of the FROM clause, read the inner query, and then jump back out to see what the outer query does with it. The difference becomes dramatic once queries get more layered — nested derived tables inside derived tables quickly turn into a wall of parentheses, while chained CTEs (covered on the next page) stay flat and readable.

CTE Scope: One Statement Only

A CTE only exists for the duration of the single statement it is attached to. Once that statement finishes executing, the CTE is gone — you cannot reference customer_totals from the example above in a separate query run afterward. This is different from creating an actual table.

Note

This is the key contrast with a temporary table: a temporary table is created once with CREATE TEMPORARY TABLE, persists for the rest of the database session (or until dropped), and can be queried, filtered, and joined across many separate statements. A CTE, by contrast, is really just syntax for naming a subquery — it is recomputed every time the statement runs and cannot be reused across statements. See the Temporary Tables page for that longer-lived alternative.

A CTE Can Be Referenced More Than Once

Within its one statement, a CTE can be referenced multiple times, which avoids repeating the same subquery logic twice — a real readability and maintenance win even when there is no multi-step pipeline involved.

SQL

WITH order_stats AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT
  (SELECT COUNT(*) FROM order_stats WHERE order_count > 5)  AS frequent_customers,
  (SELECT AVG(total_spent) FROM order_stats)                AS avg_spent_per_customer;

A CTE is written as WITH name AS (subquery), followed by a statement that can reference name.
CTEs are usually functionally equivalent to a derived table in FROM, but read top-to-bottom instead of nested.
A CTE is scoped to the single statement it is attached to — it does not persist afterward.
A temporary table, by contrast, persists for the session and can be queried across multiple statements.