SQL JOIN vs Subquery — When to Use Each

JOIN vs Subquery

Both JOINs and subqueries can retrieve related data from multiple tables, but they work differently and have distinct trade-offs.

The Same Query, Two Ways

Using a JOIN:

SELECT o.id, o.total, c.name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE c.country = 'US';

Using a subquery:

SELECT o.id, o.total
FROM orders AS o
WHERE o.customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);

When JOINs Are Better

• You need columns from both tables in the SELECT clause.
• Multiple columns from the related table are required.
• Aggregate data across the relationship (e.g., SUM of order items per customer).
• The optimizer can use hash joins or merge joins for large datasets.

When Subqueries Are Better

• Existence checks: WHERE EXISTS (SELECT 1 FROM ...) is often clearer and can short-circuit early.
• The subquery result is small: IN (SELECT ...) with a small result set is very readable.
• Correlated subqueries for row-by-row calculations that do not map cleanly to a join.
• Derived tables in the FROM clause for pre-aggregation before joining.

Performance Reality

Modern query optimizers (PostgreSQL 10+, MySQL 8+, SQL Server 2016+) often convert IN subqueries into semi-joins automatically, producing the same execution plan as an explicit JOIN. However, correlated subqueries that execute once per row can still be dramatically slower than a join.

Readability Rule of Thumb

If the subquery makes the intent clearer ("give me orders where the customer is in the US"), use a subquery. If you need to pull data from both tables, use a JOIN. When in doubt, run EXPLAIN on both and compare.

CTEs as a Middle Ground

Common Table Expressions (WITH clauses) let you break complex logic into named, readable steps. They combine the clarity of subqueries with the flexibility of joins.