Subquery vs JOIN — When to Use Each (With Performance Analysis)

1. The Demo Dataset

Every example below runs against two small tables. Create them once and follow along — the NULL trap in section 5 depends on this exact data.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(50),
  region_id   INT              -- intentionally nullable
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  amount      DECIMAL(10,2),
  ordered_at  DATE
);

INSERT INTO customers VALUES
  (1, 'Alice', 10),
  (2, 'Bob',   20),
  (3, 'Carol', NULL),   -- the NULL that breaks NOT IN later
  (4, 'Dave',  30);

INSERT INTO orders VALUES
  (101, 1, 250.00, '2026-08-01'),
  (102, 1,  90.50, '2026-08-15'),
  (103, 2, 480.00, '2026-08-20'),
  (104, 4,  15.00, '2026-09-01');
-- Note: Carol (customer_id = 3) has no orders.

Four customers, four orders. Alice ordered twice, Carol never did, and Carol's region_id is NULL. Keep those three facts in mind.

2. The Semantic Difference: Sets vs Rows

A JOIN and a subquery answer different questions, and picking the one that matches your question is step one.

A JOIN says: "combine rows from two tables where a condition holds." The result is a new relation — its row count depends on matches on both sides. If Alice has two orders, customers JOIN orders produces two Alice rows. That's a feature when you want order-level detail and a bug when you wanted one row per customer.

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

A subquery says: "evaluate this inner question first (or per-row), then use its result in the outer query." Depending on placement it returns a single value (scalar), a single column (used with IN/EXISTS), or a derived table (in the FROM clause). A membership test reads naturally as a subquery:

SELECT c.name
FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

Both queries above return customers who ordered — but the JOIN needs DISTINCT to avoid duplicating Alice, while the IN version returns each customer at most once by definition. That deduplication guarantee is a semantic difference, not a style preference.

3. The Performance Truth: Optimizers Rewrite Most Subqueries

The folklore says JOINs are faster because subqueries "execute the inner query for every row." On modern engines this is simply not what happens for non-correlated subqueries. The optimizer flattens them.

Run the IN query from section 2 through EXPLAIN in PostgreSQL:

EXPLAIN
SELECT c.name
FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

The plan shows a Hash Semi Join — the same semi-join operator you'd get from a deduplicated JOIN. There is no inner loop, no per-row re-execution. MySQL 8 behaves the same way; since 5.6 it converts IN (subquery) into a semi-join using materialization or first-match strategies. SQL Server has done this since roughly forever.

So for a plain, non-correlated membership filter, the subquery-vs-JOIN performance debate is dead. Write whichever communicates intent better — for "find customers who ordered," IN/EXISTS usually reads more directly than JOIN + DISTINCT.

4. The Real Trap: Correlated Subqueries

A subquery becomes correlated when it references a column from the outer query. The inner query can no longer be evaluated once on its own — its result depends on which outer row is currently being processed:

-- Correlated: the inner WHERE mentions c.customer_id
SELECT c.name,
       (SELECT MAX(o.amount)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS biggest_order
FROM customers c;

Conceptually, the database evaluates the inner SELECT MAX(...) once per customer. With 4 customers that's nothing. With 10 million customers and no index on orders.customer_id, that's 10 million sequential scans of the orders table. This is the construct behind most genuinely slow "subquery" queries — not subqueries in general.

Mitigations, in order of preference:

1. Index the correlation column. An index on orders(customer_id, amount) turns each inner execution into an index-only lookup. Often this alone makes the correlated form perfectly fast.
2. Rewrite as a JOIN against a pre-aggregated derived table — one scan, one grouping, one join:

   SELECT c.name, m.biggest_order
   FROM customers c
   LEFT JOIN (
     SELECT customer_id, MAX(amount) AS biggest_order
     FROM orders
     GROUP BY customer_id
   ) m ON m.customer_id = c.customer_id;

   Note the LEFT JOIN: the correlated scalar subquery returns NULL for Carol (no orders), and only LEFT JOIN reproduces that. An INNER JOIN would silently drop her.
3. Use a window function (PostgreSQL, MySQL 8+, SQL Server):

   SELECT DISTINCT name,
          MAX(amount) OVER (PARTITION BY c.customer_id) AS biggest_order
   FROM customers c
   JOIN orders o ON o.customer_id = c.customer_id;

Also know that some engines decorrelate automatically — PostgreSQL rewrites many EXISTS/IN correlated subqueries into joins — but scalar correlated subqueries in the SELECT list are frequently executed per row even there. Check the plan (section 8) before assuming.

5. IN vs EXISTS — and the NOT IN NULL Trap

The classic IN vs EXISTS comparison. For positive tests, both work and modern optimizers produce the same semi-join plan:

-- Version A: IN
SELECT c.name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

-- Version B: EXISTS (correlated, but decorrelated by the optimizer)
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id);

Alice, Bob, Dave. Identical results, identical plans on PostgreSQL and MySQL 8. Pick either; EXISTS scales slightly better in intent because it explicitly stops at the first match and never worries about the inner query returning many rows.

The negation is where they diverge — dangerously

Now find customers with no orders. The NOT EXISTS version does what you expect:

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);
-- Returns: Carol ✓

The NOT IN version looks equivalent. It is not:

SELECT c.name FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);
-- Returns: (empty set) — if any orders.customer_id is NULL

Why? x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. The last comparison yields UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN — which the WHERE clause treats as false. One NULL in the subquery column and NOT IN returns zero rows for the entire table. No error, no warning, just a silently empty result. In our demo orders.customer_id happens to be clean, so the query returns Carol — but the moment a nullable FK column admits a single NULL, it breaks.

Rules that follow:

• Use NOT EXISTS (or a LEFT JOIN ... IS NULL anti-join) for "rows with no match." It is NULL-safe by construction.
• If you must use NOT IN, guard the subquery: WHERE col NOT IN (SELECT x FROM t WHERE x IS NOT NULL) — or declare the column NOT NULL.
• The trap is asymmetric: IN with NULLs merely fails to match the NULL (harmless); NOT IN with NULLs destroys the whole result (catastrophic).

6. When a Subquery Is the Clearer Choice

JOINs aren't automatically "more readable." When you need exactly one value per outer row, a scalar subquery in the SELECT list states the intent without dragging a second table into the main relation:

SELECT c.name,
       (SELECT COUNT(*) FROM orders o
        WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

Compare the JOIN equivalent — it needs a GROUP BY that only exists to undo the fan-out the JOIN created:

SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

The scalar-subquery version keeps the outer query's grain obvious: one row per customer, full stop. It's especially good for annotating a report ("...and also show each customer's most recent order date") where adding JOIN + GROUP BY would ripple through the whole query. Two honest caveats: it's still a correlated subquery, so index the correlation column, and each additional scalar subquery is another pass — if you need three values from the same table, that's a signal to switch to the aggregate-JOIN form.

Scalar subqueries also shine as single-value parameters, where no JOIN is even possible:

SELECT name FROM customers
WHERE region_id = (SELECT region_id FROM customers WHERE name = 'Bob');
-- and the classic:
SELECT * FROM orders
WHERE amount = (SELECT MAX(amount) FROM orders);

7. When a JOIN Is the Only Option

A scalar subquery returns one row, one column — that's enforced by the engine, and violating it at runtime is an error, not a silent wrong answer. So the moment you need multiple columns from the related table, subqueries stop being a real option:

-- Need BOTH the biggest order's amount AND its date: JOIN wins
SELECT c.name, o.amount, o.ordered_at
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id
JOIN (
  SELECT customer_id, MAX(amount) AS max_amount
  FROM orders GROUP BY customer_id
) mx ON mx.customer_id = o.customer_id
    AND mx.max_amount  = o.amount;

You could fake it with two parallel scalar subqueries — one for amount, one for ordered_at — but you'd scan the orders table twice and risk the two subqueries disagreeing on ties. JOIN once, get a coherent row.

Other cases where JOIN is mandatory or clearly better:

• Aggregating over the related table's rows with multiple output columns (count + sum + max in one pass).
• Filtering on related-table columns combined with selecting them (WHERE o.ordered_at >= '2026-09-01' while also showing o.amount).
• Order-level output: when the result grain should be orders, not customers, a JOIN expresses that directly.
• Three or more tables: chained JOINs with explicit ON conditions stay auditable; nested subqueries at that depth do not.

8. Verifying With EXPLAIN: A Practical Workflow

Don't argue about subquery vs JOIN performance — measure it. This is the workflow:

Step 1 — Write both versions. The subquery form and the JOIN form of the same question.

Step 2 — EXPLAIN both (no ANALYZE yet). EXPLAIN shows the planned strategy without executing:

EXPLAIN SELECT c.name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

EXPLAIN SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

In PostgreSQL, if both plans show Hash Semi Join (or the second shows HashAggregate over a Hash Join), you've proven the rewrite: the semi-join plan is the cheaper one because it skips dedup entirely. In MySQL, run EXPLAIN FORMAT=TREE and look for "Nested loop semijoin" versus a plain join plus temporary table for DISTINCT.

Step 3 — EXPLAIN ANALYZE for real numbers (PostgreSQL; MySQL 8.0.18+ supports ANALYZE too). This executes the query and annotates the plan with actual rows and milliseconds:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);

Compare actual time on the top node and the buffers count. For the correlated scalar subquery from section 4, watch for the inner scan node's loops value — loops=1000000 is your smoking gun.

Step 4 — Check indexes at the join/correlation column. A sequential scan on the inner side of a semi-join is fine for small tables and fatal for large ones. CREATE INDEX ON orders (customer_id); then re-EXPLAIN and confirm the plan switched to an index scan or index-only scan.

Step 5 — Keep the readable version when plans match. If both forms produce equivalent plans and timings, ship the one a colleague can understand in ten seconds. Readability is a real maintenance-cost metric; don't pay it away for a performance difference that doesn't exist.

Reading raw EXPLAIN trees is an acquired taste. Paste your plan into our EXPLAIN visualizer to get a node-by-node breakdown with costs and row estimates highlighted — it's the fastest way to spot the loops=1,000,000 pattern.

FAQ — Subquery vs JOIN