Common table expressions (WITH)

Why name a subquery?

Derived tables work, but they nest inside out: to understand the query you start in the middle and read outwards. With two or three steps, the brackets pile up and the logic hides. A common table expression, or CTE, moves each step to the top of the query and gives it a name. You read the query like a recipe: first this, then this, then the answer.

WITH totals AS (
  SELECT c.name, SUM(oi.qty * oi.price) AS total
  FROM customers AS c
  JOIN orders AS o       ON o.customer_id = c.id
  JOIN order_items AS oi ON oi.order_id = o.id
  WHERE o.status <> 'Cancelled'
  GROUP BY c.id, c.name
)
SELECT name, total
FROM totals
WHERE total > (SELECT AVG(total) FROM totals)
ORDER BY total DESC;
-- Ravi Kumar | 6497
-- Asha Verma | 4678

The shape is WITH name AS (query), followed by the main statement. The CTE exists only while this one statement runs; it is not saved anywhere. Using a CTE twice, as above, is something a derived table cannot do without copying the whole subquery.

Several CTEs, each building on the last

Separate the CTEs with commas, and write WITH only once. A later CTE may read an earlier one.

WITH dept_stats AS (
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employees
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
),
company AS (
  SELECT AVG(salary) AS avg_all FROM employees
)
SELECT d.name, s.headcount,
       ROUND(s.avg_salary) AS avg_salary,
       ROUND(s.avg_salary - c.avg_all) AS vs_company
FROM dept_stats AS s
JOIN departments AS d ON d.id = s.dept_id
CROSS JOIN company AS c
ORDER BY vs_company DESC;
-- Engineering | 4 | 140000 |  40000
-- Finance     | 2 |  86500 | -13500
-- Sales       | 3 |  80667 | -19333
-- HR          | 1 |  80000 | -20000

Monthly revenue, then filter

WITH monthly AS (
  SELECT substr(o.order_date, 1, 7) AS month,
         SUM(oi.qty * oi.price) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.id
  WHERE o.status <> 'Cancelled'
  GROUP BY month
)
SELECT month, revenue FROM monthly WHERE revenue >= 3000 ORDER BY month;
-- 2025-01 | 3779
-- 2025-03 | 7167

CTE, subquery or view?

Does a CTE make queries faster?

Not by itself; it is mainly about readability. How it runs depends on the engine. Materialising means computing the CTE once and storing the result in a temporary table; inlining means pasting it into the main query as if it were a subquery, so the planner can optimise everything together. PostgreSQL before version 12 always materialised CTEs, which sometimes made them slower. Since version 12 it inlines a CTE used once, materialises one used more than once, and lets you choose with AS MATERIALIZED or AS NOT MATERIALIZED. SQLite (3.35+) accepts the same hints. SQL Server treats a CTE like an inline view and does not store it. MySQL supports CTEs from version 8.0. If speed matters, check the query plan (module 10).

Common mistakes

• Writing WITH before every CTE. Write WITH once, then name AS (...), name2 AS (...), separated by commas.
• A comma after the last CTE, before the main SELECT: a syntax error.
• Expecting the CTE to exist in the next statement. It lives for one statement only; use a view or a table to keep it.
• A semicolon between the CTE and its SELECT, which ends the statement early. The main query follows the closing bracket directly.