SQL Subqueries vs JOINs Explained — Which One Should You Use?

Many SQL problems can be solved two ways: with a JOIN, or with a subquery in the WHERE, SELECT, or FROM clause. Both can return correct results, but they don't always perform the same, and they don't always read equally well. This guide compares the two approaches directly, with real examples, so you know which one to reach for.

The Same Problem, Two Ways

Suppose you want customers who have placed at least one order. Here is the JOIN version:

-- JOIN version
SELECT DISTINCT c.id, c.name
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

And here is the same result using a subquery with EXISTS:

-- Subquery version
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Both return the same rows. The JOIN needs DISTINCT because a customer with three orders would otherwise appear three times — the subquery avoids that problem entirely because it only checks existence, never pulls in order rows.

Subqueries in WHERE, SELECT, and FROM

Subqueries show up in three places, each with a different purpose:

• WHERE clause — filter rows using IN, EXISTS, or a comparison to a scalar value
• SELECT clause — pull in a single computed value per row, like a correlated aggregate
• FROM clause — treat a derived result set as if it were a table (a "derived table" or inline view)

-- Subquery in SELECT: total orders per customer, without a GROUP BY on the outer query
SELECT
  c.name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Subquery in FROM: derived table
SELECT region, AVG(monthly_total) AS avg_monthly
FROM (
  SELECT region, MONTH(order_date) AS m, SUM(order_total) AS monthly_total
  FROM orders
  GROUP BY region, MONTH(order_date)
) AS region_monthly
GROUP BY region;

Correlated Subqueries — The Performance Trap

A correlated subquery references a column from the outer query, which means it can run once per outer row instead of once total. On a small table this is invisible; on a large table it can be dramatically slower than the equivalent JOIN:

-- Correlated subquery: potentially re-executed per row of customers
SELECT c.name,
  (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;

-- Often faster as a JOIN against a pre-aggregated result
SELECT c.name, last_orders.last_order
FROM customers c
LEFT JOIN (
  SELECT customer_id, MAX(order_date) AS last_order
  FROM orders
  GROUP BY customer_id
) AS last_orders ON last_orders.customer_id = c.id;

Modern optimizers (MySQL 8+, PostgreSQL) can sometimes flatten correlated subqueries into JOINs automatically, but you shouldn't count on it — check the query plan with EXPLAIN if the table is large.

IN vs EXISTS vs JOIN for Filtering

1. IN (subquery) — clean and readable for a small, non-null list of values; can be slow if the subquery returns a huge result set
2. EXISTS (subquery) — generally the fastest option for existence checks because it stops scanning as soon as one match is found
3. JOIN — best when you need columns from both tables in the output, not just a filter

-- IN: readable for a value list
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

-- EXISTS: usually faster, and NULL-safe
SELECT * FROM products p
WHERE EXISTS (
  SELECT 1 FROM categories c WHERE c.id = p.category_id AND c.active = 1
);

One important gotcha: NOT IN with a subquery that can return NULL values silently returns zero rows in standard SQL. Use NOT EXISTS instead when checking for absence — it doesn't have this problem.

Quick Decision Guide

• Need columns from both tables in the result? Use a JOIN
• Just checking whether a related row exists? Use EXISTS
• Comparing against a single aggregate value (e.g. "above the average")? Use a subquery in WHERE
• Need to pre-aggregate before joining? Use a subquery in FROM (or a CTE for readability)
• Checking for absence of a related row? Use NOT EXISTS, not NOT IN

Frequently Asked Questions