A query that answers a real business question is rarely one SELECT. "Which departments pay above the company average, and by how much?" needs the average per department, the company average, and then a comparison — three steps. Written with derived tables, the steps nest inside one another and the query reads inside out. A common table expression (CTE) gives each step a name at the top of the statement, so the query reads top to bottom like a recipe. CTEs are standard SQL, supported by MySQL since 8.0, and the shape most interviewers expect a multi-step answer in. This lesson covers the syntax, chaining, reuse, how CTEs compare to subqueries, views and temporary tables, and the habits that keep them readable.

The syntax

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
)
SELECT dept, ROUND(avg_salary, 2) AS avg_salary
FROM dept_avg
WHERE avg_salary > 60000
ORDER BY avg_salary DESC, dept;

WITH name AS ( query ) defines the CTE; the statement that follows reads name exactly like a table — in FROM, in a join, in a subquery. The CTE exists only for this one statement: the next query cannot see dept_avg, and nothing is stored.

You can name the columns in a list instead of with aliases inside:

WITH dept_avg (dept, avg_salary) AS (
  SELECT dept, AVG(salary) FROM employees GROUP BY dept
)
SELECT * FROM dept_avg;

Both are fine; the inner aliases keep each name beside its expression and are the more common style.

Chaining CTEs

Write WITH once and separate the definitions with commas. Each CTE may read every CTE defined before it:

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
),
company AS (
  SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT d.dept,
       ROUND(d.avg_salary, 2) AS avg_salary,
       ROUND(d.avg_salary - c.avg_salary, 2) AS above_company
FROM dept_avg d
CROSS JOIN company c
WHERE d.avg_salary > c.avg_salary
ORDER BY above_company DESC, d.dept;

company has exactly one row, so the cross join attaches the company average to every department row — a common, deliberate use of CROSS JOIN. Read the statement top to bottom: averages per department, the company average, then the comparison. Each step can be tested alone by running its SELECT, which is the most practical benefit of the style.

A longer pipeline follows the same pattern: clean the raw rows, aggregate them, rank or filter, then present.

WITH paid AS (               -- step 1: the rows that count
  SELECT customer_id, amount FROM orders WHERE status = 'paid'
),
totals AS (                  -- step 2: one row per customer
  SELECT customer_id, SUM(amount) AS total FROM paid GROUP BY customer_id
),
grand AS (                   -- step 3: one number
  SELECT SUM(total) AS grand_total FROM totals
)
SELECT t.customer_id,
       t.total,
       ROUND(100 * t.total / g.grand_total, 1) AS pct
FROM totals t CROSS JOIN grand g
ORDER BY t.total DESC, t.customer_id;

Reusing one CTE

A derived table is written where it is used; to use the same result twice you must write it twice. A CTE is written once and referenced as often as needed:

WITH monthly AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT cur.month, cur.revenue, prev.revenue AS prev_revenue
FROM monthly cur
LEFT JOIN monthly prev
  ON prev.month = DATE_FORMAT(DATE_SUB(CONCAT(cur.month, '-01'), INTERVAL 1 MONTH), '%Y-%m')
ORDER BY cur.month;

Here monthly is joined to itself to put each month beside the previous one. (The window-functions module does this more simply with LAG; the self-join of a CTE is the classic pre-window answer and still common.) When a CTE is referenced more than once, MySQL may materialize it — compute it once into an internal temporary table — rather than repeat the work.

CTEs versus subqueries, views and temporary tables

Performance. A CTE is not automatically faster or slower than the equivalent derived table. MySQL's optimizer usually merges a CTE referenced once into the outer query, so predicates are pushed down and indexes on the base tables are used exactly as if you had written one flat query. A CTE referenced several times may be materialized once. PostgreSQL before version 12 always materialized CTEs (an "optimization fence" — filters outside did not reach inside), which is the origin of the folklore that CTEs are slow; since 12 it inlines them too, unless you write AS MATERIALIZED. SQL Server never materializes a CTE: it re-expands it every time it is referenced, so a CTE used three times may run three times.

When to use which. CTEs for structure inside one query. A view when many queries need the same definition. A temporary table when a heavy intermediate result is read by several statements or needs an index.

Rules worth knowing

• Scope. A CTE is visible to the statement it belongs to, including subqueries inside it. CTEs defined inside a subquery are visible only there.
• Names shadow tables. A CTE named orders hides the real orders table for that statement. Avoid it; it confuses every reader.
• Not in the CTE's own body. A plain CTE cannot refer to itself — that is what WITH RECURSIVE (next lesson) is for.
• With data changes. In MySQL, WITH may precede UPDATE and DELETE (and the SELECT part of INSERT … SELECT), so the same named steps can drive a change. PostgreSQL goes further, with data-modifying CTEs (WITH moved AS (DELETE … RETURNING *) INSERT …); MySQL has no equivalent.
• ORDER BY inside a CTE is not an order. The final SELECT decides the row order; always end with an ORDER BY that fixes it completely.

Readability habits

Name each CTE after what its rows are (dept_avg, paid_orders, first_purchase), not t1, cte2. Keep each step to one idea: filter, or aggregate, or join — not all three. Put a short comment on the step whose purpose is not obvious from its name. In an interview, building the answer as a chain of CTEs lets you talk through each step and test it, and it is far easier to fix one step than to unpick a five-level nested subquery.

Pitfalls

• Expecting a CTE to persist into the next statement.
• Referring to a later CTE from an earlier one — order of definition matters.
• Assuming the CTE's ORDER BY orders the result.
• Assuming a CTE is cached or faster without checking the plan.
• Writing WITH twice instead of one WITH with commas.

Key takeaways

• WITH name AS (query) names a result set for one statement; the main query reads it like a table.
• Chain CTEs with commas; each can read the ones defined before it, so a query becomes a list of named steps.
• A CTE can be referenced several times, which a derived table cannot without being repeated.
• CTEs are about readability: MySQL usually merges them like derived tables, and may materialize one used repeatedly.
• Use views for definitions shared across queries and temporary tables for results shared across statements.