Common Table Expressions (WITH)

As queries grow, nested subqueries become hard to read from the inside out. A Common Table Expression (CTE), introduced with WITH, gives a subquery a name at the top of the statement. The main query then reads like a sequence of clear steps.

Basic syntax

SQL

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
)
SELECT order_id, round(total, 2) AS total
FROM order_totals
WHERE total > 100
ORDER BY total DESC;

Result 2 rows

order_id	total
10	119.98
7	108.99

The CTE order_totals exists only for this statement. The main query uses it just like a table.

Several CTEs in a pipeline

SQL

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
),
customer_totals AS (
  SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
  FROM order_totals
  GROUP BY customer_id
)
SELECT c.name, ct.orders, round(ct.spent, 2) AS spent,
       round(ct.spent / ct.orders, 2) AS avg_order
FROM customer_totals AS ct
JOIN customers AS c ON c.id = ct.customer_id
ORDER BY spent DESC
LIMIT 5;

Result 5 rows

name	orders	spent	avg_order
Ben Carter	2	168.98	84.49
Ana Pereira	3	168.88	56.29
George Brown	2	137.9	68.95
Hana Novak	1	119.98	119.98
Chloé Martin	2	117	58.5

Separate CTEs with commas. Each can use the ones defined before it, so a complex calculation becomes a readable pipeline: totals per order → totals per customer → final report.

Using a CTE more than once

SQL

WITH dept_pay AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  WHERE department_id IS NOT NULL
  GROUP BY department_id
)
SELECT d.name, round(dp.avg_salary) AS avg_salary,
       round(dp.avg_salary - (SELECT AVG(avg_salary) FROM dept_pay)) AS vs_avg_of_departments
FROM dept_pay AS dp
JOIN departments AS d ON d.id = dp.department_id
ORDER BY avg_salary DESC;

Result 4 rows

name	avg_salary	vs_avg_of_departments
Engineering	79750	16521
Sales	62667	-563
Marketing	59500	-3729
Support	51000	-12229

A derived table would have to be written out twice; a CTE is defined once and referenced as often as needed.

CTEs vs subqueries vs views

	Scope	Best for
Subquery	One place in one query	Short, one-off conditions
CTE	One statement	Breaking complex queries into named steps
View	Saved in the database	Reusable queries shared by many statements (lesson 47)

Most databases optimise CTEs just like subqueries. (Older PostgreSQL versions always computed CTEs separately; since version 12 they're inlined unless you write AS MATERIALIZED.)

CTEs with data changes

SQL

WITH slow_sellers AS (
  SELECT p.id
  FROM products AS p
  LEFT JOIN order_items AS oi ON oi.product_id = p.id
  GROUP BY p.id
  HAVING COALESCE(SUM(oi.quantity), 0) < 2
)
UPDATE products
SET price = round(price * 0.9, 2)
WHERE id IN (SELECT id FROM slow_sellers);

SELECT name, price FROM products WHERE id IN (3, 11, 12);

Result 3 rows

name	price
Night Sky Atlas	40.5
Wooden Train Set	35.91
Smart Plug	13.49

A CTE can prepare the rows for an UPDATE, DELETE or INSERT. Here, products that sold fewer than two units get a 10% discount.

Try it yourself

Using CTEs, find the best-selling product (by units) in each category.