SQL Subqueries vs Joins — Performance and When to Use Each
Fast recall
Quick revision
Core answer
Whether you understand when the planner can optimize a subquery to a join and when it cannot, particularly the performance trap of correlated subqueries.
Writing correlated subqueries without realizing they execute once per outer row, turning an O(n) join into an O(n²) operation.
Explain that EXISTS subqueries often outperform IN subqueries on large datasets because EXISTS short-circuits on the first match, while IN must build the entire result set.
Interview-ready guide
Interview answer
Joins and subqueries can often express the same query — and modern query planners frequently rewrite one into the other. The performance difference only becomes critical in one specific scenario: the correlated subquery.
When they're equivalent — let the planner decide
- An uncorrelated subquery doesn't reference the outer query's row — PostgreSQL and MySQL 8.0+ routinely rewrite these into equivalent joins at the planning stage, so the execution plan is the same regardless of which form you write
- In these cases, write whichever reads more clearly; the performance difference is negligible because the planner will optimize it either way
The correlated subquery trap
- A correlated subquery references the outer query's current row —
SELECT * FROM orders o WHERE o.total > (SELECT AVG(total) FROM orders WHERE customer_id = o.customer_id)— which means the subquery runs once for every row in the outer scan, turning an O(n) operation into O(n squared) - At 100,000 orders across 10,000 customers, that's 100,000 subquery executions — where a single join with a GROUP BY pre-computes the average once per customer
EXISTS vs IN — for existence checks
EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)short-circuits on the first match — as soon as one order is found, the check succeeds — which makes it faster thanIN (SELECT customer_id FROM orders), which must collect and materialize the full set before comparing- Use
NOT EXISTSinstead ofNOT INwhen nullable columns are involved —NOT INreturns no rows if any value in the subquery is NULL due to three-valued logic;NOT EXISTShandles nulls correctly
Use a join when you need columns from both tables. Use a subquery when the logic reads more naturally as a nested question. Use EXISTS for existence checks on large tables.
SQL Subqueries vs Joins — Performance and When to Use Each
Direct Answer
Joins and subqueries often express equivalent logic but differ in readability and performance. Modern planners flatten uncorrelated subqueries into joins, so the execution plan is identical. The trap is the correlated subquery: it references the outer row and re-executes once per outer row, turning an O(n) operation into O(n²). Use JOIN when you need columns from both tables, EXISTS for existence checks on large tables, and IN only for small static lists.
Common Mistakes & Pitfalls
Writing correlated subqueries without realizing they execute once per outer row, turning an O(n) join into an O(n²) operation.
Check your understanding
Answer these without looking back at the article.
- When would the planner fail to flatten an uncorrelated subquery?
- How does EXISTS differ from IN for NULL handling?
- What is a semi-join and how does the planner implement it?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.