Skip to content
Quantoros

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

Related questions