SQL / SUBQUERIES AND CTES
Common table expressions with WITH
Use WITH to give a subquery a name that acts like a table for one statement, reference that name more than once, and know exactly when it goes out of scope.
What you will learn
- Prefix a statement with WITH name AS (...) and leave out the semicolon before the SELECT
- Reference one CTE twice in a statement instead of pasting the same subquery twice
- Rename output columns up front with WITH totals(customer, total) AS (...)
- Spot the silent shadowing when a CTE name matches a real table
Understanding Common table expressions with WITH
A WITH clause sits in front of one statement and binds a name to a SELECT. Inside that statement the name can be used anywhere a table name is legal: in FROM, on either side of a join, inside a scalar subquery in the WHERE clause. The name lives in a name space that exists only while that statement is being planned and run, so nothing is written to the catalog, no permissions are involved, and the next statement in your script cannot see it.
Read WITH as a definition, not as a step that executes first. The engine may splice the body into the outer query, or compute it once into a work table and read that twice; both choices must return the same rows, and which one it picks is a planning decision, not something the syntax promises. The concrete benefit is the name itself: once a result set is named, it can appear in two places in the same statement without being typed twice, and the query can be read top to bottom in the order the logic happens.
A CTE body is closed off from the query that uses it. It can read real tables, but it cannot see a column from the outer query, so its WHERE clause has no way to mention the row currently being processed outside. An optional column list after the name, as in WITH totals(customer, total) AS (...), renames every output column positionally and saves aliasing expressions inside the body. Keep that name distinct from real table names, because within the statement the CTE wins the collision and no warning is raised.
CREATE TABLE orders (
id INTEGER,
customer TEXT,
amount INTEGER
);
INSERT INTO orders (id, customer, amount) VALUES
(1, 'ada', 120),
(2, 'brian', 300),
(3, 'clara', 40),
(4, 'ada', 140),
(5, 'clara', 60),
(6, 'brian', 100),
(7, 'dinesh', 140);
WITH customer_totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > (SELECT AVG(total) FROM customer_totals)
ORDER BY total DESC;WITH gives a subquery a name that behaves like a table for exactly one statement, and only that statement.
Worked examples
Naming the CTE's columns
An explicit column list renames the output of the body, and the CTE is then joined back to the base table it came from (same orders table as above).
WITH top_amount(customer, best) AS (
SELECT customer, MAX(amount)
FROM orders
GROUP BY customer
)
SELECT o.id, o.customer, o.amount
FROM orders o
JOIN top_amount t
ON o.customer = t.customer
AND o.amount = t.best
ORDER BY o.id;Example explained
Line 1top_amount(customer, best) names both output columns positionally, so the bare MAX(amount) becomes best with no alias inside the body.
Line 2The CTE takes the alias t in FROM exactly like a table would, which is what lets o.customer and t.customer be told apart.
Line 3The join condition matches on customer and amount together, so it keeps the rows that hit each customer's own maximum, not the global maximum.
Line 4A customer with two orders at the same maximum amount would produce two rows here, because the join has no way to break the tie.
One CTE consumed twice
The same named result set supplies both the per-customer rows and the grand total, so the GROUP BY is written once (same orders table as above).
WITH customer_totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
)
SELECT t.customer,
t.total,
ROUND(100.0 * t.total / g.grand, 1) AS pct
FROM customer_totals t
CROSS JOIN (SELECT SUM(total) AS grand FROM customer_totals) g
ORDER BY t.total DESC;Example explained
Line 1customer_totals appears twice in one statement, once as t and once inside the subquery that sums it, which is the reuse a plain inline subquery cannot give you.
Line 2CROSS JOIN pairs every customer row with the single row holding grand = 900, making the whole-set number available on each row.
Line 3100.0 forces decimal division; written as 100 * t.total / g.grand with integers the share truncates to 44 instead of 44.4.
Line 4ORDER BY t.total DESC sorts the final result, because rows coming out of a CTE have no order of their own.
Important notes
The number of names in the optional column list must match the number of columns the body returns, otherwise the statement is rejected before any rows are produced.
Whether the body runs once or once per reference is the planner's choice, so do not rely on a CTE to freeze the value of a volatile expression such as random(); PostgreSQL 12 and later even lets you force the decision with MATERIALIZED or NOT MATERIALIZED.
Common mistakes
Ending the WITH block with a semicolon before the main SELECT: the statement is now incomplete, and the following SELECT fails with an unknown-table error because the CTE was never attached to it.
Assuming the name persists, then selecting from it in the next statement or in a later query in the same session; it no longer exists, so you get 'no such table' even though the first statement succeeded.
Naming a CTE after a table that already exists: no error is raised, the CTE quietly shadows the table for that statement, and the query returns plausible but wrong numbers.
Try it yourself
Change, predict, then run
Using the orders table from above, write one statement whose CTE customer_totals returns customer and order_count, then return only the customers whose order_count is above the average order_count across all customers, referencing the CTE twice.
Open the SQL workspaceCheck your understanding
A database already contains a table named active_users. Someone runs: WITH active_users AS (SELECT * FROM users WHERE last_login > '2026-01-01') SELECT count(*) FROM active_users; What happens?
- The statement fails, because the name active_users already exists in the database
- The count comes from the real active_users table, since permanent tables take precedence over CTE names
- The count comes from the CTE, which hides the real table for the duration of that one statement
- The CTE replaces the real table, so later queries in the session also see only the filtered rows
Show answer
Inside a statement, a CTE name is resolved before base table names, so count(*) reads the filtered rows and the real table is simply not consulted; nothing outside the statement changes. The first option is tempting because CREATE TABLE with an existing name does fail, but a CTE is not a schema object, so there is no collision to report and the mistake stays silent.