SQL Common Table Expressions (CTE): Write Readable Queries With WITH

As queries grow, nested subqueries become hard to read: you have to start in the middle and work outwards. A Common Table Expression (CTE) lets you give a subquery a name at the top of the query and then use it like a table. Queries read top to bottom, one step at a time.

Syntax#

WITH cte_name AS (
  SELECT ...
)
SELECT ...
FROM cte_name;

The CTE exists only for the duration of this one statement.

A first CTE#

Employees who earn more than their department's average. First compute the averages, then compare:

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.department = e.department
WHERE e.salary > d.avg_salary;

name  | department  | salary | avg_salary
------+-------------+--------+-----------
Meera | Engineering | 81000  | 79500
Priya | Sales       | 61000  | 56500

Step 1 (the CTE) has a clear name, dept_avg. Step 2 (the main query) reads like plain English: "join each employee to their department's average and keep those above it".

Several CTEs in a row#

Separate CTEs with commas. Each one can use the CTEs defined before it:

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
),
big_spenders AS (
  SELECT *
  FROM customer_totals
  WHERE total > 100
)
SELECT c.name, b.total
FROM big_spenders b
JOIN customers c ON c.customer_id = b.customer_id
ORDER BY b.total DESC;

name | total
-----+------
Ravi | 560
Asha | 370

Write WITH only once, at the start. The first CTE totals every customer's orders; the second keeps totals over 100; the main query adds the names.

Using a CTE more than once#

A CTE can be referenced several times in the main query, which a plain subquery cannot do without repeating itself. Revenue per region and each region's share of the total:

WITH region_totals AS (
  SELECT region, SUM(revenue) AS total
  FROM monthly_sales
  GROUP BY region
)
SELECT region,
       total,
       ROUND(100.0 * total / (SELECT SUM(total) FROM region_totals), 1) AS pct
FROM region_totals
ORDER BY total DESC;

region | total | pct
-------+-------+-----
North  | 550   | 55.8
South  | 435   | 44.2

region_totals is used both as the main table and inside the percentage calculation.

CTE vs subquery vs view#

In most databases a CTE performs about the same as the equivalent subquery. (Older PostgreSQL versions, before 12, always computed CTEs separately, which could be slower; newer versions inline them when possible.)

CTEs with INSERT, UPDATE and DELETE#

PostgreSQL, SQL Server and SQLite allow a CTE before data-changing statements, which helps when the rows to change are found in several steps:

WITH inactive AS (
  SELECT customer_id
  FROM customers c
  WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
)
DELETE FROM customers
WHERE customer_id IN (SELECT customer_id FROM inactive);

Recursive CTEs#

A CTE can even refer to itself, which lets SQL walk hierarchies and generate sequences. That is a topic of its own: Recursive CTEs.

Database support#

CTEs work in PostgreSQL, SQL Server, Oracle, SQLite, and MySQL 8.0+. MySQL 5.7 and older do not support them.

Common mistakes#

Practice#

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY month
)
SELECT month,
       revenue,
       (SELECT AVG(revenue) FROM monthly) AS avg_month
FROM monthly
ORDER BY month;

month   | revenue | avg_month
--------+---------+----------
2026-01 | 250     | 340
2026-02 | 680     | 340
2026-03 | 90      | 340

Key takeaway#