What is the difference between JOIN and subquery for filtering?
SQLLevel: IntermediateJOINs Deep Dive
What is the difference between JOIN and subquery for filtering?
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
Both can work, but they have different readability and performance tradeoffs depending on the database.
-- Using JOIN
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.id = o.customer_id;
-- Using subquery
SELECT *
FROM customers
WHERE id IN (SELECT customer_id FROM orders);
-- Using EXISTS (often the most readable for "existence" checks)
SELECT *
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
For "does this record exist in another table?" — EXISTS is often clearest and the optimizer usually handles it well. For "give me columns from both tables" — JOIN is the right tool. IN with a subquery can get slow on large subquery results.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT