Common Table Expressions (CTEs)
Writing readable queries with CTEs
As SQL queries grow, they can become difficult to read.
Imagine we want to:
calculate how much each customer has spent
keep only customers whose total spending is above a certain amount
join those customers back to the
customerstable
We could write all of that using nested subqueries.
But PostgreSQL gives us another way to organize the query: a Common Table Expression, or CTE.
What is a CTE?
A CTE is a named auxiliary query that can be used by the rest of a SQL statement.
A CTE is defined using:
1WITHFor example:
1234567891011WITH first_five_orders AS ( SELECT id, customer_id, total_amount FROM orders WHERE id <= 5)SELECT *FROM first_five_ordersORDER BY id;The CTE is:
1first_five_ordersand its query is:
123456SELECT id, customer_id, total_amountFROM ordersWHERE id <= 5The rest of the statement can refer to first_five_orders almost as though it were a table.
You should see:
1234567 id | customer_id | total_amount----+-------------+-------------- 1 | 2 | 10.01 2 | 3 | 10.02 3 | 4 | 10.03 4 | 5 | 10.04 5 | 6 | 10.05The CTE exists only for this SQL statement.
It does not create a permanent table in the database.
The basic syntax
The general structure is:
12345WITH cte_name AS ( query)SELECT ...FROM cte_name;A useful way to read this is:
12345WITH define a named resultthen use that result in the main query12345678910orders │ ▼CTE: first_five_orders │ ▼main SELECT │ ▼resultA more useful example
Suppose we want to calculate total spending for every customer.
We can first define that calculation:
12345678910111213WITH customer_totals AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id)SELECT customer_id, total_spentFROM customer_totalsORDER BY total_spent DESCLIMIT 10;You should see:
123456789101112 customer_id | total_spent-------------+------------- 7 | 15830.60 4 | 15685.30 5 | 15390.40 2 | 15250.10 6 | 15230.50 3 | 15085.20 1 | 11820.00 9000 | 4609.90 8999 | 4609.80 8998 | 4609.70The seeded data gives customers 1 through 7 many more orders than everyone else, so they stand clearly above the rest. That gap is what the next section filters on.
The CTE handles this part of the problem:
12345678910orders │ ▼GROUP BY customer_id │ ▼SUM(total_amount) │ ▼customer_totalsThe main query then works with the result.
This separation can make a query easier to understand because each part has a clear purpose.
Filtering a CTE result
Once the CTE has calculated total_spent, the main query can filter that result:
12345678910111213WITH customer_totals AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id)SELECT customer_id, total_spentFROM customer_totalsWHERE total_spent > 5000ORDER BY total_spent DESC;You should see:
123456789 customer_id | total_spent-------------+------------- 7 | 15830.60 4 | 15685.30 5 | 15390.40 2 | 15250.10 6 | 15230.50 3 | 15085.20 1 | 11820.00Notice that:
1SUM(total_amount)is calculated inside the CTE.
The outer query sees:
12customer_idtotal_spentas columns in the CTE result.
Joining a CTE to another table
A CTE can also participate in joins.
For example:
12345678910111213141516WITH customer_totals AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id)SELECT c.id, c.full_name, ct.total_spentFROM customer_totals ctINNER JOIN customers c ON ct.customer_id = c.idWHERE ct.total_spent > 5000ORDER BY ct.total_spent DESC;You should see:
123456789 id | full_name | total_spent----+------------+------------- 7 | Customer 7 | 15830.60 4 | Customer 4 | 15685.30 5 | Customer 5 | 15390.40 2 | Customer 2 | 15250.10 6 | Customer 6 | 15230.50 3 | Customer 3 | 15085.20 1 | Customer 1 | 11820.00The query now has two clear steps:
123456789101112Step 1orders │ ▼customer_totalsStep 2customer_totals + customers │ ▼final resultThe CTE does not replace joins, grouping, or aggregation.
It gives us a way to organize those operations into named pieces.
Multiple CTEs
A single WITH clause can define more than one CTE.
Separate them with commas:
123456789101112131415161718192021WITH customer_totals AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id),high_value_customers AS ( SELECT customer_id, total_spent FROM customer_totals WHERE total_spent > 5000)SELECT c.full_name, hvc.total_spentFROM high_value_customers hvcINNER JOIN customers c ON hvc.customer_id = c.idORDER BY hvc.total_spent DESC;You should see:
123456789 full_name | total_spent------------+------------- Customer 7 | 15830.60 Customer 4 | 15685.30 Customer 5 | 15390.40 Customer 2 | 15250.10 Customer 6 | 15230.50 Customer 3 | 15085.20 Customer 1 | 11820.00The second CTE can use the result of the first CTE:
12345678910111213orders │ ▼customer_totals │ ▼high_value_customers │ ▼join customers │ ▼final resultThis lets a complicated query read more like a sequence of transformations.
CTEs and subqueries
The same logic can often be written using a subquery.
For example:
1234567891011SELECT customer_id, total_spentFROM ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id) customer_totalsWHERE total_spent > 5000;or using a CTE:
123456789101112WITH customer_totals AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id)SELECT customer_id, total_spentFROM customer_totalsWHERE total_spent > 5000;Neither form is automatically better.
For a small query, the subquery may be perfectly clear.
As the query becomes more complicated, named CTEs can make the individual steps easier to understand.
A useful mental model is:
12345subquery→ put one query inside another queryCTE→ give a query result a name and use that name laterA CTE can be referenced more than once
After defining a CTE, the same statement can reference it multiple times.
For example:
12345678910111213WITH customer_totals AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id)SELECT ROUND( (SELECT AVG(total_spent) FROM customer_totals), 2 ) AS average_spending, (SELECT MAX(total_spent) FROM customer_totals) AS highest_spending;You should see:
123 average_spending | highest_spending------------------+------------------ 4128.81 | 15830.60customer_totals is defined once and used twice.
This can be useful when several parts of one statement need the same intermediate result.
How PostgreSQL executes a CTE that is referenced multiple times has performance implications. We will look at that in the materialization chapter.
CTEs are scoped to one statement
A CTE is not a permanent database object.
After this statement finishes:
1234567WITH first_five_orders AS ( SELECT * FROM orders WHERE id <= 5)SELECT *FROM first_five_orders;you cannot run:
12SELECT *FROM first_five_orders;as a separate statement.
PostgreSQL answers with:
1ERROR: relation "first_five_orders" does not existfirst_five_orders no longer exists.
The CTE exists only within the statement containing the WITH clause.
CTEs can also modify data
CTEs are commonly used with SELECT, but PostgreSQL also allows data-modifying statements inside WITH.
For example, create a temporary table:
12345CREATE TEMP TABLE demo_tasks ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, completed BOOLEAN NOT NULL);Add some rows:
12345INSERT INTO demo_tasks (id, title, completed)VALUES (1, 'Write query', true), (2, 'Review query', false), (3, 'Deploy application', true);Now use a DELETE inside a CTE:
12345678WITH deleted_tasks AS ( DELETE FROM demo_tasks WHERE completed = true RETURNING id, title)SELECT *FROM deleted_tasksORDER BY id;The DELETE removes the matching rows.
The important part is:
1RETURNING id, titleThose returned rows become the result that the rest of the statement can read through:
1deleted_tasksYou should see:
1234 id | title----+-------------------- 1 | Write query 3 | Deploy applicationData-modifying CTEs have some additional execution rules and should be used deliberately. For most application queries, ordinary SELECT CTEs are what you will encounter most often.
Remove the demonstration table:
1DROP TABLE demo_tasks;The core mental model
A CTE lets you take part of a query:
1calculate somethinggive that result a name:
1customer_totalsand then continue querying it:
12345678910customer_totals │ ▼filter │ ▼join │ ▼final resultThe important point is:
CTEs let you break a larger SQL statement into named, understandable pieces.