Use EXISTS when you only need to know whether a matching row exists, not what it is. It stops at the first match for each outer row and cannot multiply rows. That makes it the natural form for a semi-join: "customers who have at least one order".
The three options side by side
-- EXISTS: one row per customer, stops at first matching order SELECT c.id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); -- IN: fine for this too SELECT c.id FROM customers c WHERE c.id IN (SELECT customer_id FROM orders); -- JOIN: customer 7 with 5 orders appears 5 times SELECT c.id FROM customers c JOIN orders o ON o.customer_id = c.id;
The JOIN version returns a row per order. If you wanted the customers once, you would need DISTINCT on top, and that is the "hide the fan-out" smell. The EXISTS and IN versions give each customer once.
EXISTS or IN
For positive checks, most modern optimizers plan EXISTS and IN the same way, so pick what reads best. IN is clean for short, fixed lists: status IN ('PAID', 'SHIPPED').
The big difference is the negative form. NOT IN breaks when the subquery returns a NULL (the whole predicate becomes UNKNOWN and returns nothing). NOT EXISTS has no such problem. So default to NOT EXISTS for "has no matching row" questions.
When a join is right
Use JOIN when you need columns from the other table. Then fan-out is real data: 5 orders mean 5 rows, and you asked for them. If you need one row per customer plus something from the orders, aggregate first (latest order date, order count) and then join to that.
A short way to say it in an interview: EXISTS is for "is there one", JOIN is for "give me the matching rows", and NOT EXISTS is the safe way to say "there is none".