Choosing Between Subqueries, CTEs, and Joins in SQL
Subqueries, CTEs, and joins are three different ways to compose multiple relational operations in a single query. Choosing between them is a judgment about readability, correctness, performance, and reuse, applied to the specific problem at hand.
Before this Multi-CTE Query Architecture, Correlated Subqueries, LATERAL Joins
Subqueries, CTEs, and joins can often produce identical results. Choosing between them is a judgment call about readability, correctness, and performance — applied to the specific problem at hand.
The clearest illustration is finding each customer's most recent order date. Your manager wants a table showing every customer alongside when they last purchased. You have a customers table and an orders table. There are at least three valid approaches: a correlated subquery, a pre-aggregated subquery in the FROM clause, or a pre-aggregated CTE. Each produces identical output. The right choice depends on table size, how many times the intermediate result is needed, and which version communicates its intent most clearly.
Here are the two most common forms:
The correlated version executes once per customer row. Compare it to the pre-aggregated join form:
-- Pre-aggregated join: better at scale
SELECT c.id AS customer_id,
o.last_order_date
FROM customers c
LEFT JOIN (
SELECT customer_id, MAX(ordered_at)::date AS last_order_date
FROM orders
GROUP BY customer_id
) o ON o.customer_id = c.idBoth return identical results. At small scale, choose whichever reads more clearly. At large scale, EXPLAIN ANALYZE will tell you if the correlated version is the bottleneck.
CTEs: use when the query has multiple logical stages
CTEs communicate intent. A CTE with a descriptive name tells a reader what an intermediate result represents before they even read the logic that produces it. For a query with more than two or three transformations, this readability advantage compounds — each CTE is a named layer that reads as a step in a sequence. The tradeoff: CTEs referenced more than once are materialized by default in PostgreSQL, which creates a physical boundary the planner cannot optimize across. For CTEs referenced exactly once, PostgreSQL 12+ typically inlines them, so materialization cost usually isn't a concern.
LATERAL: use when the per-row operation returns multiple rows
Correlated subqueries in SELECT are limited to one scalar value per outer row. LATERAL lifts that restriction — it's a correlated subquery that can return multiple rows and columns. When the per-row operation needs to return the top 3 events, or multiple columns per row, LATERAL is the right tool.
Joins: use when the relationship between tables is the primary thing
Joins are the natural choice when you're combining related tables and the joined result will be filtered or aggregated as a whole. Watch for fanout: when the right side of a join has multiple rows per key, pre-aggregate it in a CTE before joining to keep row counts under control.
The one thing that trips people up
Performance should be the last criterion, not the first. Readability and correctness come first. If EXPLAIN ANALYZE shows a real cost, then rewrite. Don't optimize preemptively — optimize what the plan confirms is slow.
10 Choosing Between Subqueries, CTEs, and Joins practice problems
Write a query to return the combined value of `quantity` multiplied by `unit_price` across every line item belonging to shipped `orders`.
Write a query to return each customer's name and their order count, with the count reported as a missing value for customers who have no orders on record.
Write a query to return each customer's `id`, their `order_count`, and their `total_value` — the combined `total_amount` across their `orders`.
Write a query to return each `department_id` and its `total_salary` — the combined current salary across active employees in that department.
Write a query to return each customer's name and their `total_order_value` — the combined `total_amount` across their `orders`, reported as a missing value for customers who have no orders on record.
Write a query to return each `department_id`, its `dept_salary` (combined salary across all of its records), and its `pct_of_total` (its share of the company-wide salary expenditure expressed as a percentage).
Write a query to return each `category_name` and its `revenue` — the combined line-item revenue across its products.
Write a query to return each qualifying `category_name` and its `revenue`.
Write a query to return each session's `id`, the `user_id` it belongs to, the count of `events` in that session, the earliest event timestamp, and the latest event timestamp.
Write a query to return each `department_name`, its `dept_total` (combined current salary), and its `salary_share` (its share of the company-wide current salary expenditure expressed as a percentage).
These problems are part of the Choosing Between Subqueries, CTEs, and Joins lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.