SQL / SUBQUERIES AND CTES
Choosing between joins, subqueries, and CTEs
Decide when a query needs a join, a subquery, or a CTE by reasoning about row shape, and pre-aggregate to avoid the doubled sums a double join produces.
What you will learn
- Pick a join only when you want one output row per matching pair of rows
- Use EXISTS when the other table only decides yes or no, so row counts stay fixed
- Pre-aggregate each one-to-many branch before joining so sums are not multiplied
- Choose a CTE when an intermediate result is named, reused twice, or recursive
Understanding Choosing between joins, subqueries, and CTEs
Joins, subqueries, and CTEs are not three styles for the same job; they differ in what they do to the number of rows. A join is a row multiplier and a row filter at once: every match on the right emits another output row, and an inner join with no match removes the left row entirely. A subquery in WHERE such as EXISTS can only keep or drop rows, a scalar subquery in the SELECT list can only add a column, and a subquery in FROM or a CTE introduces a new row source with its own grain. Decide the grain of the answer first (one row per customer, per order, per order item) and the construct is usually forced on you.
The classic mistake follows straight from that: joining one parent to two independent child tables and aggregating afterwards. Two orders and two calls for the same customer produce four combined rows, so SUM(amount) reports twice the real revenue and SUM(minutes) twice the real minutes, with no error anywhere. The fix is not DISTINCT, because the multiplication happens before the aggregate runs; it is collapsing each child to one row per key first, in a CTE or derived table, then joining key to key. Prefer a CTE over a derived table when the same intermediate result is needed more than once, when a name keeps the pipeline readable, or when the query is recursive.
Speed is rarely the deciding factor, because the planner rewrites spellings: IN and EXISTS both become semi-joins, and PostgreSQL 12 and later inlines a single-use non-recursive CTE, so wrapping a slow query in WITH buys nothing. What genuinely differs is when the optimizer is blocked: AS MATERIALIZED, and every CTE in PostgreSQL 11 and earlier, forces the CTE to be computed once as a fence, which helps when a small result is reused and hurts when an outer filter could have been pushed inside. Write the version whose row shape is obviously correct, then compare EXPLAIN for the two candidates instead of guessing.
CREATE TABLE orders (customer text, amount int);
CREATE TABLE calls (customer text, minutes int);
INSERT INTO orders VALUES ('Ada', 50), ('Ada', 25), ('Grace', 80), ('Linus', 40);
INSERT INTO calls VALUES ('Ada', 3), ('Ada', 7), ('Grace', 5);
-- Two independent child tables in one join: Ada's 2 orders x 2 calls = 4 rows,
-- so both sums double, and Linus (no call rows) disappears.
SELECT o.customer, SUM(o.amount) AS amount, SUM(c.minutes) AS minutes
FROM orders o
JOIN calls c ON c.customer = o.customer
GROUP BY o.customer
ORDER BY o.customer;
-- Collapse each side to one row per customer first, then join 1:1.
WITH order_totals AS (
SELECT customer, SUM(amount) AS amount FROM orders GROUP BY customer
), call_totals AS (
SELECT customer, SUM(minutes) AS minutes FROM calls GROUP BY customer
)
SELECT t.customer, t.amount, COALESCE(c.minutes, 0) AS minutes
FROM order_totals t
LEFT JOIN call_totals c ON c.customer = t.customer
ORDER BY t.customer;The construct follows from the row shape you need: joins add and remove rows, WHERE-subqueries only filter, scalar subqueries only add a column, and CTEs or derived tables define a new grain that is safe to join against.
Worked examples
Join multiplies, EXISTS filters
Shows that the same matching condition changes the row count in a join but cannot change it in EXISTS.
-- Same orders and calls tables as above.
-- JOIN: one row per (order, call) pair
SELECT o.customer, o.amount
FROM orders o
JOIN calls c ON c.customer = o.customer
ORDER BY o.customer, o.amount;
-- EXISTS: a filter, so each order row survives at most once
SELECT o.customer, o.amount
FROM orders o
WHERE EXISTS (SELECT 1 FROM calls c WHERE c.customer = o.customer)
ORDER BY o.customer, o.amount;Example explained
Line 1The join pairs each of Ada's two orders with each of her two calls, so two order rows leave as four.
Line 2EXISTS only asks whether a matching call row exists, so an order can be kept once but never duplicated.
Line 3Both queries drop Linus, whose 40 has no call row; the difference between them is duplication, not which customers qualify.
Line 4Nothing from calls can be selected outside the EXISTS block, which is exactly why the row count is safe.
Scalar subquery versus LEFT JOIN with GROUP BY
Two spellings of a per-order count that return identical rows but fail in different ways.
-- One value per outer row: the four order rows stay four rows
SELECT o.customer, o.amount,
(SELECT COUNT(*) FROM calls c WHERE c.customer = o.customer) AS calls
FROM orders o
ORDER BY o.customer, o.amount;
-- Same answer via LEFT JOIN, but now every output column must be grouped
SELECT o.customer, o.amount, COUNT(c.customer) AS calls
FROM orders o
LEFT JOIN calls c ON c.customer = o.customer
GROUP BY o.customer, o.amount
ORDER BY o.customer, o.amount;Example explained
Line 1The scalar subquery contributes a column only, so the shape of the result is fixed by orders alone.
Line 2The join version must group by customer and amount; if Ada had two orders of 50 they would merge into one row and the count would silently be wrong.
Line 3COUNT(c.customer) counts non-null matches, which is why Linus reads 0 while COUNT(*) would have counted his padded NULL row as 1.
Line 4The outputs match here, so the choice is about which failure mode you can live with, not about speed.
A CTE used twice in one statement
Demonstrates the case only a CTE handles cleanly: the same intermediate result referenced from two places.
WITH totals AS (
SELECT customer, SUM(amount) AS amount FROM orders GROUP BY customer
)
SELECT t.customer, t.amount,
t.amount - (SELECT MAX(amount) FROM totals) AS behind_top
FROM totals t
ORDER BY t.customer;Example explained
Line 1totals is named once and referenced twice: as the FROM source and inside the scalar subquery.
Line 2A derived table cannot be referenced from elsewhere in the query, so the same GROUP BY text would have to be written twice.
Line 3The subquery mentions no outer column, so it is uncorrelated and yields the single top total 80 for every row.
Line 4SUM over an int column returns bigint, so the differences print as plain integers with no decimal part.
Important notes
A CTE is not automatically a temporary table that runs once; PostgreSQL 12 and later inline a single-use non-recursive CTE, and you must write AS MATERIALIZED to force the old fence behaviour.
Aggregating after a join forces every non-aggregated column into GROUP BY, so include a key column there or rows that happen to be identical will collapse into one.
Common mistakes
Joining orders to two child tables and then calling SUM: each amount is counted once per row on the other branch, so totals are inflated by a factor nobody notices because no error is raised.
Reaching for SELECT DISTINCT to undo that inflation: DISTINCT runs after the aggregate, so the sum stays wrong, and SUM(DISTINCT amount) then discards legitimately equal amounts.
Rewriting a JOIN as EXISTS while still selecting a column from the inner table: the alias inside EXISTS is out of scope, so the query fails with a missing FROM-clause entry instead of returning rows.
Try it yourself
Change, predict, then run
Using the orders and calls tables, add returns(customer text, item text) with two rows for Ada and one for Grace, then write a single query that reports per customer the total amount, total minutes, and number of returns with all three numbers correct.
Open the SQL workspaceCheck your understanding
A report joins customers to orders and to support_tickets, then selects SUM(orders.amount) per customer. A customer with 3 orders and 4 tickets shows exactly 4x their real revenue. Which change fixes the number?
- Add SELECT DISTINCT to the outer query so the duplicated rows collapse
- Change both joins to LEFT JOIN so no customer is dropped
- Aggregate orders and tickets separately first, then join the one-row-per-customer results
- Wrap the existing query in a WITH clause and select from that CTE
Show answer
The ticket join repeats every order row four times before SUM ever sees it, so only collapsing each branch to one row per customer before the join removes the multiplication. DISTINCT is tempting, but it is applied after aggregation and cannot un-add values; LEFT JOIN and a WITH wrapper change which rows are kept and what the query is called, not how many times each order is counted.