JOIN vs Subquery: Which and When?

Use a JOIN when you are combining columns from two tables into one row of output — the row comes from the combination of the tables. Use a subquery when you are using one query's result as a filter, a value or a set for another query. The subquery produces a value that the outer query consumes; the JOIN produces a wider row.

That framing is more useful than any rule about performance, because the two are not actually different capabilities. A correlated subquery in the SELECT list can be rewritten as a LEFT JOIN with a GROUP BY, and a JOIN with a WHERE clause on the joined table can often be rewritten as a filtered subquery. The optimizer frequently turns one into the other internally.

So the choice is mostly about expressing intent. A JOIN says "these rows are related"; a subquery says "use this computed set to decide which rows matter". If the report is "customers and their order totals", a JOIN with a GROUP BY says exactly that. If it is "customers who have placed more than three orders", the filter is a threshold on an aggregate over a set, which reads naturally as a subquery or as a HAVING clause.

There are situations where one is genuinely better. A subquery in the SELECT list keeps the outer row structure intact — you get one row per customer with a computed column, without collapsing anything. A LEFT JOIN with GROUP BY gives the same result but changes the row grain, and that difference matters when you add more columns later. Conversely, a JOIN to a small lookup table is clearer than a correlated scalar subquery that fetches the same value once per row.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

-- JOIN: the two tables are combined, then filtered by a threshold.
SELECT c.name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING SUM(o.amount) > 300;

-- Subquery: the threshold is computed first, then used to pick rows.
SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 300
);

Both forms return Ada Lovelace, whose orders total 460.50. The JOIN version says "these customers are related to orders, and the relationship's total clears the threshold". The subquery version says "these customers are the ones the qualifying set points at". Same result, different mental model — and the subquery version is the one you reach for when the threshold set feeds several other queries.

The task is "list every customer with their total spend". The join version combines the tables and then aggregates; the subquery version computes the total separately and joins it back.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

-- Version A: join, then aggregate.
SELECT c.id, c.name, ROUND(SUM(o.amount), 2) AS total_spend
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

-- Version B: aggregate in a subquery, then join the result back.
SELECT c.id, c.name, t.total_spend
FROM customers AS c
JOIN (
  SELECT customer_id, ROUND(SUM(amount), 2) AS total_spend
  FROM orders
  GROUP BY customer_id
) AS t ON t.customer_id = c.id
ORDER BY c.id;

Both return two identical rows: Ada Lovelace with 460.50 and Grace Hopper with 75.25. Alan Turing is absent from both, because the inner join drops customers with no orders.

The difference shows up when you change the requirement. If the report should include customers with no orders, version A becomes a LEFT JOIN plus GROUP BY and needs a COALESCE around the SUM, because SUM over no matching rows is NULL. Version B becomes a LEFT JOIN to the derived table, and the total_spend column is simply NULL for those customers — no COALESCE inside the subquery needed, though you would still coalesce it at the presentation layer if you want a zero.

Version B has a structural advantage worth naming: the subquery is computed once. If the query later needs two different roll-ups of the same table — total spend and order count — the derived-table version aggregates once and joins once, whereas the version-A approach would either repeat the join or repeat the GROUP BY. That is the classic reason to prefer a derived table in reporting code.

Both forms run the same underlying plan on most engines, because the optimizer rewrites one into the other. The choice is therefore about the shape of the code and how easy it is to extend, not about speed.

Almost never, and that is worth knowing before you spend an afternoon rewriting queries. Modern optimizers treat both as ways of describing the same relational algebra, and they routinely rewrite one into the other internally. A correlated subquery in the SELECT list is frequently turned into a semi-join or an anti-join. A derived table is frequently inlined into the outer query. A subquery in FROM with a GROUP BY is frequently pushed down into an aggregate node in the join tree.

What the planner genuinely cannot do is change the semantics you asked for. If you wrote a correlated subquery that runs once per outer row, and that subquery references the outer row, the optimizer cannot turn it into a single scan unless it can prove the correlation is decorrelatable. In practice most simple cases are decorrelatable and most complex ones are not, which is why "correlated subqueries are slow" is a rule of thumb rather than a law. The version that is usually slow is the one where the subquery is correlated and returns many rows, because it genuinely does execute once per outer row.

The cases where the choice does matter are about semantics rather than syntax. A subquery in the SELECT list preserves the outer row grain — one output row per outer row — while a JOIN with GROUP BY collapses rows. If you are counting something and the two forms give different totals, that is a semantic difference, not a performance one. And a LEFT JOIN to a derived table behaves differently from a LEFT JOIN to a base table when the derived table has its own filters.

So the advice for an interview is to say that the plan is usually the same, and that you would choose based on which version states the requirement more clearly and which one keeps the row grain under control. Then reach for the plan when a query really is slow: EXPLAIN ANALYZE will show you the join order, the estimated and actual row counts, and whether the index you expected is being used. That is where the answer to a performance question lives, not in the syntax you chose.