Subqueries vs. Joins: When to Use Which

1. Three kinds of subqueries at a glance

A subquery is a query inside a query, but the term summarizes three functionally very different constructs, each with its own advantages and disadvantages compared to a join. A scalar subquery returns exactly one value and is used wherever a column expects a single expression. A derived table appears as a complete, temporary table in the FROM part and can itself be joined again. A correlated subquery references columns of the outer query and is logically re-evaluated for every outer row.

These three forms differ fundamentally in their relationship to a join. A scalar subquery often has no direct join equivalent, because it sits in a place where a join would not be syntactically possible at all. A derived table is at its core nothing other than a pre-staged join partner. A correlated subquery can frequently, but not always, be rewritten into a join or a window function. Anyone who keeps these three categories apart can answer the "subquery or join" question deliberately for each case, instead of deciding it across the board.

For the following examples we again use customers and orders, supplemented with order_items for examples with aggregation across multiple levels.

2. Scalar subqueries: a value instead of a table

A scalar subquery returns exactly one row with exactly one column and can appear anywhere a single value is expected, for example in the SELECT list, in a WHERE condition, or as a comparison value. A typical example is showing a customer's total revenue directly in the customer list, without grouping the customer table beforehand. For this case there is no simple join equivalent, because a join would produce multiple rows per customer as soon as the customer has multiple orders, the result would need to be re-aggregated afterward anyway.

A scalar subquery in the SELECT list has an important drawback: it gets executed separately per row of the outer query if it references the outer row, meaning it is correlated. With a thousand customers, that means a thousand executions of the inner query, unless the optimizer internally rewrites it into a join. Modern optimizers frequently do this automatically, but relying on it blindly is risky, especially with older database versions or more complex subqueries.

-- Scalar subquery in the SELECT list: total spent per customer
SELECT
  c.name,
  (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id = c.id) AS total_spent
FROM customers c;

-- name         | total_spent
-- Anna Berger  | 165.00
-- Tom Keller   | 300.00
-- Lisa Wolf    | NULL

-- Equivalent using a LEFT JOIN with pre-aggregation
SELECT c.name, agg.total_spent
FROM customers c
LEFT JOIN (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) agg ON agg.customer_id = c.id;

3. Derived tables: a subquery as a complete table

A derived table is a subquery that sits in the FROM part of a query and is treated there like a completely ordinary table, including its own alias and the ability to join it with other tables. The example above in the previous section with agg is already a derived table: the inner query pre-aggregates orders per customer, and the outer query joins this pre-computed table regularly with customers. Conceptually, a derived table is therefore not the opposite of a join, but a precursor to one.

The major advantage of a derived table over direct aggregation after the join lies in the correct aggregation level: if you aggregate orders first in the derived table and only join afterward, no sums get multiplied by subsequent joins, a problem that regularly occurs with multiple consecutive joins followed by aggregation. This technique thus directly solves the multiplication problem that arises when joining multiple tables across 1:n relationships.

-- WRONG: aggregating after joining two 1:n relations multiplies the sum
SELECT c.name, SUM(oi.quantity * oi.price) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.name;
-- inflates totals when a customer has multiple orders with multiple items

-- RIGHT: pre-aggregate per order first in a derived table
SELECT c.name, SUM(order_totals.total) AS total
FROM customers c
JOIN (
  SELECT o.customer_id, o.id AS order_id, SUM(oi.quantity * oi.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id, o.id
) order_totals ON order_totals.customer_id = c.id
GROUP BY c.name;

4. Correlated subqueries and their join equivalent

A correlated subquery references a column of the outer query inside its WHERE condition and can therefore not be evaluated independently of it. The classic case is an EXISTS condition that checks whether at least one matching inner row exists for an outer row. Functionally this often corresponds to the same result as an INNER JOIN followed by DISTINCT, but syntactically and in the execution plan the two can differ significantly.

The most important difference between a correlated subquery with EXISTS and a corresponding join shows up as soon as the inner table has multiple matching rows per outer row: the join multiplies the outer row per match, while EXISTS only checks whether a match exists at all and therefore returns the outer row exactly once. Anyone who only wants to know whether a relationship exists, without needing the details of the inner rows, is often both more correct and more performant with EXISTS than with a join plus DISTINCT.

-- Correlated subquery with EXISTS: customers who have placed at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Join-based equivalent requires DISTINCT to avoid duplicated rows
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id;
-- both return the same names, but the join can produce intermediate
-- duplicate rows before DISTINCT removes them again

5. Readability: when a subquery is clearer than a join

Beyond performance, readability is a legitimate criterion for choosing between subquery and join. An EXISTS condition conveys the business intent "has at least one matching row" more directly than a join followed by DISTINCT, whose purpose only becomes clear on closer reading. Something similar applies to IN with a static or simple subquery list, which is often easier to scan than an equivalent join with a filter.

Conversely, a join becomes more readable as soon as several columns from the joined table are needed in the result. A subquery typically returns only one value or a yes/no piece of information, while a join provides an arbitrary number of columns from the second table in the same result row. If you need more than one piece of information from the joined table, a join is almost always the clearer and more direct choice, because otherwise multiple subqueries would pile up for the same logical relationship.

A good rule of thumb: subqueries for existence checks, single aggregate values and filter lists, joins for everything where multiple columns of the joined table should appear in the result. This rule covers most practical cases without having to weigh the decision anew for every query.

6. Performance: where subquery and join actually differ

In modern relational databases, the performance difference between a well-written subquery and an equivalent join is small to nonexistent in many cases, because the query optimizer translates both into the same internal execution plan. Relevant differences arise mainly in three situations: non-correlated subqueries in an IN condition with very large result sets, correlated subqueries that the optimizer cannot automatically decorrelate, and old or weaker optimizers that fundamentally handle certain subquery forms worse.

A correlated subquery that the optimizer cannot decorrelate is, in the worst case, actually executed once per outer row, which leads to quadratic instead of linear runtime on large tables. The most reliable way to rule out this risk is to look at the actual execution plan with EXPLAIN respectively EXPLAIN ANALYZE, instead of relying on assumptions about optimizer behavior.

-- Check whether the optimizer decorrelates a subquery into a join
EXPLAIN ANALYZE
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Look for "Hash Join" or "Semi Join" in the plan output.
-- A "Nested Loop" re-executed per outer row signals a non-decorrelated
-- subquery and is worth investigating for large tables.

7. When the optimizer treats both identically

PostgreSQL, MySQL from version 8.0 onward and SQL Server all have optimizers that automatically rewrite many subqueries into semantically equivalent joins, a process referred to as subquery decorrelation or subquery unnesting. An IN subquery with a static value list is as a rule executed as a semi-join, regardless of whether it is written as IN (SELECT ...) or as an explicit join with DISTINCT, the resulting execution plan is practically identical.

This optimization capability has limits: very complex correlated subqueries with several nested conditions, aggregate functions in the correlated condition, or subqueries in OR-connected conditions are not decorrelated by some optimizers. In such cases, a manual rewrite into a join or a derived table often delivers measurably better results than the automatic optimization. The only reliable way to know this for a specific query is to compare the execution plans of both variants on realistic data volumes.

8. Common table expressions as a third path

Besides subquery and join, the common table expression, introduced with WITH, offers a third path that syntactically corresponds to a named derived table, but is defined once at the start of the query and can then be referenced multiple times. For the pure question "subquery or join", a CTE is mostly equivalent to a corresponding derived table in the FROM, but it improves readability substantially once several building-on-each-other intermediate steps are needed.

In some databases, especially older PostgreSQL versions before 12, a CTE acted as a so-called optimization fence and was materialized separately, which could degrade performance compared to an equivalent subquery. Since PostgreSQL 12, a CTE is treated inline by default whenever possible, which mostly resolves the performance difference to a subquery. Anyone running an older database version should explicitly check their version's behavior before relying on identical behavior.

-- CTE version of the pre-aggregated derived table from section 3
WITH order_totals AS (
  SELECT o.customer_id, o.id AS order_id, SUM(oi.quantity * oi.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id, o.id
)
SELECT c.name, SUM(order_totals.total) AS total
FROM customers c
JOIN order_totals ON order_totals.customer_id = c.id
GROUP BY c.name;

9. Subquery types in direct comparison

The table below summarizes the three subquery forms and shows how they differ regarding their join equivalent, their typical usage and their performance risk.

This overview shows: the "subquery or join" question is not a single decision, it depends on the specific subquery type. A derived table is practically always unproblematic, while a scalar correlated subquery always deserves a look at the execution plan before it ends up in production code.

10. Summary

The decision between subquery and join depends heavily on the specific subquery type. Scalar subqueries solve cases where a single value is needed and a join would not make sense at all. Derived tables are essentially prepared join partners and reliably solve the problem of multiplied sums after multiple joins. Correlated subqueries with EXISTS check existence without unnecessary duplicates, while a join with DISTINCT often achieves the same purpose more cumbersomely.

Modern optimizers automatically decorrelate many subqueries into joins, so the performance difference is often small in practice. Still, you should not rely on this blindly: a look at the actual execution plan with EXPLAIN ANALYZE reliably shows whether a specific subquery is decorrelated or has to be re-executed per outer row.

11. FAQ: Subqueries vs. Joins