Common Table Expressions (WITH)

A CTE is a subquery with a name, declared before the query that uses it. It does nothing a derived table cannot — and it changes how a query reads, which on anything non-trivial is the difference between maintainable and not.

SQL▶ Run
WITH dept_payroll AS (
  SELECT dept_id, ROUND(SUM(salary), 2) AS payroll
  FROM employees
  GROUP BY dept_id
)
SELECT d.name AS department, p.payroll
FROM departments d
JOIN dept_payroll p ON p.dept_id = d.dept_id
ORDER BY p.payroll DESC;

Compare with the derived-table version in lesson 24. Identical result. The difference is that the aggregation is named and sits at the top, so you read "first compute payroll per department, then join it to names" in that order — rather than finding the aggregation buried in the middle of a FROM.

Several CTEs at once

Separate them with commas. WITH appears only once:

SQL▶ Run
WITH staff AS (
  SELECT dept_id, COUNT(*) AS headcount
  FROM employees
  GROUP BY dept_id
),
payroll AS (
  SELECT dept_id, ROUND(SUM(salary), 2) AS total
  FROM employees
  WHERE salary IS NOT NULL
  GROUP BY dept_id
)
SELECT d.name       AS department,
       s.headcount,
       p.total      AS payroll
FROM departments d
LEFT JOIN staff   s ON s.dept_id = d.dept_id
LEFT JOIN payroll p ON p.dept_id = d.dept_id
ORDER BY d.name;

The empty department appears with NULLs, because both joins are LEFT. That is lesson 20 doing its job inside a bigger query.

A CTE can use an earlier CTE

They are readable in order, which lets you build a calculation in steps:

SQL▶ Run
WITH paid AS (
  SELECT dept_id, salary FROM employees WHERE salary IS NOT NULL
),
dept_avg AS (
  SELECT dept_id, ROUND(AVG(salary), 2) AS avg_salary
  FROM paid
  GROUP BY dept_id
)
SELECT dept_id, avg_salary
FROM dept_avg
ORDER BY dept_id;

Each step is a query you can run on its own by selecting from it — which is the practical reason to prefer CTEs while developing. A five-level nested subquery cannot be debugged in pieces; a five-CTE query can, by changing the final SELECT to read from whichever step you doubt.

The fan-out fix, written properly

Lesson 22 showed SUM(budget) inflating from under two million to over six once employees joined in. Here is the correct version, which is the shape you will reach for constantly: aggregate the many-side first, then join one row to one row.

SQL▶ Run
WITH staff AS (
  SELECT dept_id, COUNT(*) AS headcount
  FROM employees
  GROUP BY dept_id
)
SELECT ROUND(SUM(d.budget), 2)         AS total_budget,
       SUM(COALESCE(s.headcount, 0))   AS total_staff
FROM departments d
LEFT JOIN staff s ON s.dept_id = d.dept_id;

The true budget, and a headcount alongside it, in one row. Each department contributes exactly one row to the join, so nothing is counted twice.

What a CTE is not

• Not necessarily faster. On PostgreSQL before version 12 a CTE was an optimisation fence: it was materialised, and the planner could not push filters into it. From 12 onward CTEs are inlined when it is safe, with MATERIALIZED / NOT MATERIALIZED to force the choice. On other databases, treat "a CTE is faster" and "a CTE is slower" as equally unfounded until measured.
• Not a temporary table. It exists for the duration of the one statement. Reference it three times and the database may compute it three times.
• Not universally supported in ancient versions. MySQL gained CTEs in 8.0. Everything current supports them.

When to use which

Common mistakes

• WITH repeated before each CTE — it appears once; the rest are commas.
• A trailing comma before the final SELECT — a syntax error that reads oddly.
• Assuming a CTE is computed once — reference it twice and it may run twice.
• Wrapping everything in CTEs out of habit — a two-line query does not need a preamble.

Interview question

What is a CTE and when would you use one instead of a subquery?

A named subquery declared with WITH, scoped to the statement. Use one when the result is needed more than once (a derived table cannot be referenced twice), when naming the step makes the query readable, or when you need recursion. Adding that CTEs are not automatically an optimisation — and that PostgreSQL's behaviour changed in 12 — is the answer of someone who has profiled one.

Check yourself

1. Rewrite lesson 24's derived-table query as a CTE.
2. Why can a CTE be referenced twice when a derived table cannot?
3. What does "optimisation fence" mean, and which database is it associated with?