EXISTS vs IN vs JOIN: How to Actually Choose

Three completely different-looking queries can answer the same question — "which customers have placed an order?" — and it's genuinely unclear from the syntax alone which one you should reach for:

-- IN
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- EXISTS
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- JOIN
SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id;

All three can return the same result set. They are not interchangeable, though, and the differences aren't just style — they change what the query is actually asking, how it behaves around NULL, and what happens to your row count.

The real distinction: existence check vs. row-producing join

IN and EXISTS both ask a yes/no question per row: "is there a match?" Neither one multiplies your result. A customer with 40 orders still appears exactly once in the IN or EXISTS version, because the subquery is just a filter — it never joins the orders table's rows into the output.

JOIN does something structurally different: it produces one output row per match, not per customer. A customer with 40 orders produces 40 joined rows. That's why the JOIN version above needs DISTINCT bolted on to get back to "one row per customer" — without it, you'd get the row-explosion problem that comes up constantly whenever a one-to-many relationship gets joined and not aggregated or deduplicated on purpose.

So the first real question isn't "which is faster" — it's "do I need columns from the other table, or just a yes/no filter based on its existence?" If you only need customers.name and nothing from orders, reaching for JOIN means immediately having to clean up a duplication problem you created for no reason. EXISTS or IN state the actual intent directly: filter customers based on whether a match exists, full stop.

If you do need columns from both tables in the output — order dates alongside customer names, say — that's exactly when JOIN is the right tool, because now you genuinely want the joined row-per-match data, not just a filter.

The NULL trap that separates IN from EXISTS

This is the one that causes real production bugs. NOT IN and NOT EXISTS look interchangeable and are not, specifically around NULL:

-- If ANY customer_id in orders is NULL, this returns ZERO rows. All of them.
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NULL OR 1=1);

-- This is unaffected by NULLs in the subquery — it works correctly regardless
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

NOT IN expands into a chain of != comparisons ANDed together. Any NULL in that list makes one of those comparisons evaluate to UNKNOWN instead of TRUE or FALSE — and UNKNOWN anywhere in an AND chain poisons the entire condition to UNKNOWN for every row, which WHERE then filters out completely. One stray NULL in the subquery's column silently zeroes out the whole query, with no error. NOT EXISTS asks a fundamentally different question — "does at least one matching row exist" — and that question isn't affected by unrelated NULL values the way a direct equality chain is.

The practical rule: prefer NOT EXISTS over NOT IN whenever the subquery's column is nullable, which in practice means defaulting to NOT EXISTS unless you've explicitly confirmed the column can't contain NULL. Plain IN (not negated) doesn't have this failure mode — OR short-circuits on the first TRUE match, so a stray NULL elsewhere in the list doesn't matter. It's specifically the negated form where this bites.

What the query planner actually does with each

In practice, on any mature database engine, IN and EXISTS are frequently optimized to the same execution plan when they're logically equivalent — the planner is often smart enough to see through the syntax difference. This means the performance argument people reach for ("EXISTS is always faster") isn't reliably true anymore on modern Postgres, MySQL, or SQL Server. The NULL-safety difference above is real and syntax-level, unaffected by planner cleverness. The performance difference usually isn't — check an actual query plan for your specific case rather than trusting a rule of thumb from a decade-old forum post.

The decision, simplified

Need columns from the other table in your output? Use JOIN, and think deliberately about whether you need DISTINCT or an aggregation to avoid row duplication.

Just need a yes/no filter — "does a match exist"? Use EXISTS (or plain IN for small, known-non-null lists). Default to NOT EXISTS over NOT IN for the negated case, every time the subquery column could contain a NULL — which, unless you've checked, is the safer assumption.

Three tools that can produce the same rows on a good day, with three different failure modes on a bad one. The syntax similarity is what makes this worth knowing cold instead of guessing per query.

Working through exactly when a JOIN duplicates rows and when EXISTS doesn't is one of the core threads running through SQL Quest's lessons — live database, your own queries, free to start.