SQL CTE (Common Table Expressions) Guide — The WITH Clause Explained

Nested subqueries get hard to read fast — three levels deep, and even the person who wrote the query struggles to follow it a week later. Common Table Expressions (CTEs), introduced with the WITH clause, solve this by letting you name an intermediate result set and reference it like a temporary table. This guide covers the syntax, why CTEs improve readability, chaining multiple CTEs, and a simple recursive CTE example.

What is a CTE?

A CTE is a named temporary result set defined with WITH name AS (...)that exists only for the duration of the query that follows it. It is not stored on disk and is not visible to other queries — think of it as giving a subquery a label so you can reference it (even multiple times) later in the same statement.

WITH high_value_orders AS (
  SELECT customer_id, order_total
  FROM orders
  WHERE order_total > 500
)
SELECT customer_id, COUNT(*) AS big_order_count
FROM high_value_orders
GROUP BY customer_id
ORDER BY big_order_count DESC;

CTE vs Nested Subquery — Why Readability Wins

The same query written as a nested subquery works identically, but reads top-down in reverse — you have to find the innermost query first to understand what the outer query is filtering on:

-- Same result, as a subquery
SELECT customer_id, COUNT(*) AS big_order_count
FROM (
  SELECT customer_id, order_total
  FROM orders
  WHERE order_total > 500
) AS high_value_orders
GROUP BY customer_id
ORDER BY big_order_count DESC;

With a CTE, you read the query in the order you'd explain it out loud: "first define high-value orders, then count them per customer." This matters even more once you have several steps chained together.

Chaining Multiple CTEs

You can define several CTEs in one WITH clause, separated by commas, and each later CTE can reference the ones defined before it. This turns a multi-step analysis into a readable pipeline:

WITH monthly_sales AS (
  SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(order_total) AS total_sales
  FROM orders
  GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
avg_monthly AS (
  SELECT AVG(total_sales) AS avg_sales
  FROM monthly_sales
)
SELECT m.month, m.total_sales, a.avg_sales
FROM monthly_sales m
CROSS JOIN avg_monthly a
WHERE m.total_sales > a.avg_sales
ORDER BY m.month;

This finds every month where sales beat the overall average — without a single nested subquery. In PostgreSQL, use TO_CHAR(order_date, 'YYYY-MM') instead of DATE_FORMAT.

A Simple Recursive CTE

CTEs can also be recursive — referencing themselves to walk through hierarchical data like org charts, category trees, or number sequences. A recursive CTE has two parts joined by UNION ALL: an anchor member (the starting point) and a recursive member (which refers back to the CTE name).

-- Generate the numbers 1 through 5
WITH RECURSIVE counter AS (
  SELECT 1 AS n              -- anchor member
  UNION ALL
  SELECT n + 1                -- recursive member
  FROM counter
  WHERE n < 5
)
SELECT n FROM counter;
-- Result: 1, 2, 3, 4, 5

This same pattern scales to a manager-to-employee reporting chain: the anchor selects top-level managers (WHERE manager_id IS NULL), and the recursive member joins employees back to counter on manager_id = counter.id. MySQL requires 8.0+ for WITH RECURSIVE; PostgreSQL has supported it since version 8.4.

When to Use a CTE

• You need to reference the same derived result set more than once in a query
• The query has several logical steps and reads better top-down
• You are replacing a deeply nested subquery that is hard to debug
• You need recursion to walk a tree or hierarchy
• You want to break a complex report into named, testable pieces

CTE vs Temporary Table vs View

1. CTE — scoped to a single statement, not indexed, not reusable across queries
2. Temporary table — persists for the session, can be indexed, useful when the same result is queried many times
3. View — a saved, reusable query definition available to any query, but still recomputed each time it's used (unless materialized)

Reach for a CTE first for readability. Move to a temporary table only if you notice the optimizer re-evaluating the same CTE multiple times in a way that hurts performance on large data.

Frequently Asked Questions