Join vs EXISTS

Hook: Interviewers love this because one keyword can quietly multiply rows and create a bug that still “looks right” at first glance.

Question: What is the difference between JOIN and EXISTS in SQL?

Answer: JOIN combines rows from two tables, so it is for when you need columns from both sides or every matched pair. EXISTS only checks whether a matching row exists, so it is ideal for filtering the left table without bringing in right-table columns. The big gotcha is duplicates: a join can return many rows per parent, while EXISTS returns each left row at most once.

Interview-Ready Answer: “I use JOIN when I need data from both tables or I actually want the matched combinations. I use EXISTS when I only care whether a match is present, because it behaves like a semi-join and avoids duplicate parent rows. That distinction matters a lot when the right table can have multiple matches, such as orders, tags, or log records.”

What they mean in plain words

Detailed Explanation: A JOIN is for combining related rows. If one customer has three orders, an inner join between customers and orders produces three result rows for that customer. EXISTS is a yes/no test: “does at least one matching row exist?” It is often used with a correlated subquery, which means the subquery refers back to the current row from the outer query.

How JOIN works under the hood

1. The database reads a row from the left table.
2. It finds all matching rows on the right side using the join condition.
3. It outputs one combined row for every match.
4. If the right side has three matches, the left row appears three times. This is correct behavior, not a bug.

How EXISTS works under the hood

1. The database reads one row from the outer query.
2. It runs the subquery for that row.
3. As soon as it finds the first matching row, it can stop checking that subquery.
4. If a match was found, the outer row is kept; otherwise it is filtered out.

This “stop after the first match” behavior is the key mental model. It is why EXISTS is often a better fit for “has at least one child row?” questions.

When to use each one

Goal	Use	Why
Need columns from both tables	JOIN	Combines row data
Need only presence check	EXISTS	No row multiplication
Need all matching pairs	JOIN	Returns every match
Need “has no match”	NOT EXISTS	Null-safe anti-join

Performance and optimizer notes

There is no fixed “JOIN is always faster” rule. Modern optimizers can rewrite EXISTS into a semi-join (a join that returns only left-side rows that have a match) or an anti-join for NOT EXISTS. With a good index, such as orders(customer_id), a nested-loop EXISTS probe is often close to O(parent_rows × log(child_rows)) in practice, because the engine can seek and stop early. A join may still be just as fast when you actually need the joined columns, but if you only need existence and you write JOIN + DISTINCT, you are often forcing extra work like deduplication.

Concrete example: if you have 100,000 customers and 10,000,000 orders, a join used only to answer “which customers placed an order?” can explode into millions of intermediate rows. EXISTS lets the engine answer that question without materializing every matching order row.

Important edge cases

• EXISTS ignores the selected columns inside the subquery; SELECT 1 is the common style because only row presence matters.
• JOIN against a non-unique right table can duplicate parent rows; if you only wanted existence, that duplication is usually a mistake.
• NOT IN is dangerous when the subquery can return NULL; NOT EXISTS is usually the safer choice.
• LEFT JOIN ... WHERE right.id IS NOT NULL can sometimes mimic EXISTS, but it is easier to get wrong and can still multiply rows before filtering.

Memory idea for pressure situations: think “JOIN is for bringing people together; EXISTS is for checking the guest list.” One combines data, the other asks a yes/no question.

Real-World Example: In a checkout service, suppose you need to find customers eligible for a one-time coupon because they have at least one completed order. A developer writes a JOIN between customers and orders, but some customers have multiple completed orders. The result set now contains duplicate customer rows, so the coupon job sends repeated emails or inflates the count in the dashboard.

What goes wrong: logs show the same customer id repeated many times, the marketing queue receives duplicate messages, and the support team sees complaints like “I got the same coupon three times.” The fix is to switch to EXISTS so each customer is returned once if any matching order exists.

Follow-up & Tricky Questions:

• When is JOIN better than EXISTS? Use JOIN when you need columns from both tables or you truly want every matching pair, such as order details joined to customers.
• What is a semi-join? It is a join that returns only rows from the left side that have a match on the right. Many databases use this internally for EXISTS.
• How does EXISTS compare with IN? For non-NULL values they can look similar, but EXISTS is often clearer for correlation and safer for anti-joins. IN can be fine for a fixed list or a small subquery, but it is easier to trip over NULL behavior with NOT IN.
• Can I replace EXISTS with JOIN and DISTINCT? Sometimes yes, but it is usually less readable and may force extra work to remove duplicates after the fact.
• Does EXISTS care what I select inside the subquery? No. The database only cares whether at least one row is produced, which is why SELECT 1 is a common convention.
• Why is NOT EXISTS preferred over NOT IN? Because NOT IN becomes tricky when the subquery can return NULL; one NULL can make the result unexpectedly empty.

• Gotcha: Does EXISTS remove duplicates? Yes, on the left side it keeps each outer row at most once, but only because it is a filter, not because it deduplicates data in the same way DISTINCT does.
• Gotcha: Is LEFT JOIN ... IS NOT NULL always equivalent to EXISTS? Not always in practice, because the join can still create many intermediate rows before the filter runs. Semantically it may be similar, but EXISTS states the intent more clearly.
• Gotcha: Is EXISTS always faster? No. The optimizer may choose similar plans for both, and the best choice depends on indexes, data size, and whether you need columns from the right table.

Common Mistakes:

• Using JOIN just to test existence. Correction: use EXISTS when you only need a yes/no filter.
• Forgetting that joins can duplicate rows. Correction: if the right side is one-to-many, expect repeated left rows unless you intentionally aggregate or dedupe.
• Using NOT IN with possible NULL values. Correction: prefer NOT EXISTS for anti-joins.
• Believing SELECT 1 inside EXISTS changes the result. Correction: the selected expression is irrelevant; only row existence matters.

Memory Hook: JOIN = photocopier — every match prints another row. EXISTS = bouncer — once one valid ID is found, the person gets in and the checking stops.

Cheat Sheet:

• JOIN combines data.
• EXISTS checks presence.
• JOIN can multiply rows.
• EXISTS is often a semi-join.
• NOT EXISTS is the safer anti-join.
• Pick semantics first, then tune with indexes.

Practice Tasks:

• Rewrite a JOIN-based “has orders” query using EXISTS.
• Take a query that uses JOIN + DISTINCT and replace it with a cleaner EXISTS version.
• Create a table with a NULL value and test NOT IN vs NOT EXISTS to see the difference.