When should you use JOINs vs subqueries?

Use JOINs when you need data from multiple tables. Use subqueries (especially EXISTS) for filtering based on existence. JOINs typically faster but subqueries can be clearer. EXISTS usually better than IN for large datasets. Test both approaches with real data.

Rewrite this JOIN as a subquery: SELECT c.name FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.amount > 1000;

SELECT c.name FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.customer_id = c.id AND o.amount > 1000

);

EXISTS subquery filters customers based on order criteria without needing DISTINCT.