Join vs IN

Why interviewers love this: it looks like a syntax question, but it really tests whether you understand row shape, duplicates, and NULL behavior.

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

Answer: A JOIN combines rows from two sources, so it is used when you want columns from both sides. IN checks membership, so it is used when you only need to filter rows based on whether a value appears in another set. The big gotcha is that a join can duplicate rows when there are multiple matches, while IN behaves like a yes/no test for each left row.

Interview-Ready Answer: I use JOIN when I need to bring columns from another table into the result, and I use IN when I only need to test whether a value exists in a set. The key difference is row shape: an inner join can multiply rows if the right side has many matches, but IN acts like a membership check and keeps the left row once. One important detail is that NOT IN can behave unexpectedly if the subquery returns NULL, so for exclusion checks I often prefer NOT EXISTS.

Simple mental model

JOIN is like merging two spreadsheets and keeping matched columns from both. IN is like checking a guest list: does this value appear in the list or not?

How they work under the hood

1. The database reads the left side row by row.
2. With an INNER JOIN, it tries to find every matching row on the right side using the join condition, then outputs one result row for each match.
3. With IN (subquery), the optimizer often turns the query into a semi-join, which means a join that only checks whether at least one match exists and then keeps the left row once.
4. If the right side has duplicates, a join repeats the left row for each duplicate match, but IN ignores duplicates because membership is either true or false.
5. SQL uses three-valued logic for NULL, which means some comparisons are not true or false but unknown. That is why NOT IN can be dangerous when the subquery returns a NULL.

When to use each one

• Use JOIN when you need extra columns from the other table, such as customer names, product prices, or department labels.
• Use IN when you only need to filter by membership, such as users whose IDs appear in a blocked list or orders whose status is in a small set.
• Use EXISTS when you want to test whether a related row exists, especially for correlated checks and safer exclusion logic.

Comparison

Performance and complexity

There is no universal winner. Modern optimizers often rewrite IN into a semi-join or a hash lookup, so the plan can be almost the same as EXISTS. If the database uses a hash semi-join, the rough cost is O(N + M) time with O(M) memory, where N is the left table size and M is the right table size. A bad nested-loop plan without an index can drift toward O(N × M), which becomes painful fast: 1 million left rows times 100 thousand right rows is 100 billion comparisons in the worst case.

Practical rule: if the right side is small and indexed, either form can be fast. If the right side is large, the optimizer matters more than the syntax. Always check the execution plan for real workloads, because the same SQL text can run very differently on 10 thousand rows versus 10 million rows.

Important edge cases

• JOIN on a non-unique key can create duplicate output you did not expect.
• NULL IN (...) is not true, so it usually filters out in a WHERE clause.
• NOT IN with any NULL in the subquery can return no rows at all.
• A LEFT JOIN is not the same as IN; it keeps unmatched rows and fills the right side with NULLs.

Real-World Story: Imagine a checkout service that blocks orders from a fraud list. The team first wrote an inner JOIN from orders to blocked_customers to find risky accounts. It worked at first, but the fraud table had multiple history rows per customer, so the same order showed up twice in the nightly report and the alert job sent duplicate Slack messages.

Later, someone changed the filter to NOT IN for the clean-customer report, but one NULL slipped into the subquery because of a bad import. Overnight the report returned zero rows, the dashboard showed an impossible drop to zero clean orders, and the logs contained a clue like processed_rows=0 even though traffic was normal. The fix was to use NOT EXISTS and to enforce NOT NULL on the key column.

This is why the difference matters in production: the wrong choice can change counts, trigger duplicate side effects, or silently hide data.

Follow-up & Tricky Questions:

• When would you choose JOIN over IN? Use JOIN when you need columns from both tables, or when you want one-to-many detail rows, such as order lines with product names.
• When would you choose IN over JOIN? Use IN when you only need to filter by membership, especially if you do not care about columns from the other table.
• What is the difference between IN and EXISTS? Both test membership, but EXISTS is a row-by-row existence check and is often clearer for correlated subqueries and exclusion logic.
• Can IN be fast on a large table? Yes. Optimizers often turn it into a semi-join or indexed lookup, so the real plan matters more than the syntax alone.
• How do you avoid duplicate rows from a join? First check whether the join key is unique on the right side; if not, deduplicate the right table or switch to a membership test if that is the real requirement.
• Tricky: Is LEFT JOIN the same as IN? No. A left join keeps unmatched left rows and fills the right columns with NULL, while IN filters rows based on membership.
• Tricky: Why does NOT IN sometimes return nothing? Because one NULL in the subquery makes the comparison unknown, so SQL cannot prove any row is not in the set.
• Tricky: If the right table has duplicate keys, does IN return duplicate rows? No. Duplicates in the subquery do not change membership; they only matter to a join, which can multiply rows.

Common Mistakes

• Using JOIN when you only need filtering. Correction: if you do not need columns from the other table, prefer IN or EXISTS for clearer intent.
• Forgetting that joins can multiply rows. Correction: check whether the join key is unique on the right side; if not, deduplicate or rethink the query.
• Using NOT IN with nullable subqueries. Correction: use NOT EXISTS, or at minimum filter out NULLs explicitly.
• Assuming syntax decides speed. Correction: the optimizer and indexes decide the plan; always inspect the execution plan on real data.

Memory Hook: JOIN brings the person into the room; IN just checks the guest list.

Cheat Sheet

• JOIN = combine rows and columns.
• IN = membership test.
• EXISTS = existence test, often safer.
• Duplicate matches multiply rows in joins.
• NOT IN + NULL is a classic trap.
• Check the execution plan for large tables.

Practice Tasks

• Write an INNER JOIN and an IN query that return the same customers, then compare the output shapes.
• Add a duplicate row to the right table and see how the join changes while IN does not.
• Replace a broken NOT IN query with NOT EXISTS and verify that rows return correctly even when the subquery contains NULL.