Choosing Between Subqueries, CTEs, and Joins in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Multi-CTE Query Architecture, Correlated Subqueries, LATERAL Joins
What are Subqueries vs CTEs vs Joins in SQL?
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:
SELECT c.name, (SELECT MAX(ordered_at)::date FROM orders o WHERE o.customer_id = c.id) AS last_order_date FROM customers c ORDER BY last_order_date DESC NULLS LAST LIMIT 10
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.
When should you use a CTE instead of a subquery?
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.
When is LATERAL the right choice?
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.
When is a plain join the right choice?
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.
Should performance decide between a CTE, a subquery and a join?
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.
Practice Subqueries vs CTEs vs Joins in SQL
Scenario: Brightlane's finance team needs the combined value of every line item belonging to a shipped order, computed from line-item data rather than any pre-stored total.
Task: Write a query to return the combined value of quantity multiplied by unit_price across every line item belonging to shipped orders.
Assumptions:
- A shipped order has
statusequal to'shipped'. - A line item's value is
quantitymultiplied byunit_price. - The result is a single combined value across every line item belonging to a shipped order.
Output:
- One row, holding the combined shipped line-item value.
- Columns in this order:
total_shipped_item_value.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Subqueries vs CTEs vs 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).
Start learning to practice all 10 Subqueries vs CTEs vs Joins problems, with instant grading and mastery tracking.
Common questions about Subqueries vs CTEs vs Joins
Are CTEs and subqueries interchangeable?
For a single intermediate result used once, they usually produce the same rows and the choice is about readability. They stop being interchangeable when the result is needed twice, because a CTE is named once and a subquery has to be repeated.
Which should you reach for first?
Whichever states the intent most plainly for the query in front of you. Correctness and readability come first because they are permanent; a performance difference is worth chasing once a plan shows one, and not before.
When is a join clearly the right answer?
When the relationship between two tables is the thing you are asking about, and the combined rows will be filtered or aggregated as a whole. Watch the row count as you add each join, because a one-to-many relationship quietly multiplies what follows.
