Detailed explanation
IN, EXISTS, and JOIN can all connect information across tables, but they express different intentions. IN tests whether a value belongs to a set supplied as literals or by a single-column subquery. EXISTS tests whether a subquery returns at least one row. JOIN combines matching rows from two inputs, allowing columns from both to appear in the result. Interviewers expect you to explain these semantics before discussing performance.
For example, SELECT c.* FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); returns customers who have at least one order. The subquery is correlated because it references c.id from the outer query. SELECT 1 is conventional: EXISTS checks row presence, not the selected value. An equivalent positive membership query is SELECT c.* FROM customers c WHERE c.id IN (SELECT o.customer_id FROM orders o);. Repeated customer IDs in orders do not multiply customer rows in either query.
By contrast, SELECT c.* FROM customers c JOIN orders o ON o.customer_id = c.id; returns one result row for each matching customer-order pair. A customer with three orders therefore appears three times, even though only customer columns are selected. This multiplication is appropriate when you need individual order details. For a pure existence check, EXISTS communicates the requirement directly without using DISTINCT to remove repeated results. These predicates preserve qualifying outer rows; they do not deduplicate rows already present in the outer input.
NULLs and execution plans require care. IN uses SQL’s three-valued logic: without an equal match, a NULL in the set can make the result UNKNOWN. Consequently, NOT IN can unexpectedly filter out nonmatching rows when its subquery contains NULL. NOT EXISTS avoids this particular trap, although its correlation predicate still follows normal NULL comparison rules. Neither IN nor EXISTS guarantees a fixed execution strategy: IN need not materialise a list, and optimisers may transform either into a semi-join. Indexes, statistics, and actual execution plans determine performance.
Key points to remember
- IN checks membership; EXISTS checks row presence; JOIN combines rows.
- EXISTS and IN do not multiply outer rows when multiple inner matches exist.
- JOIN returns every matching pair, potentially repeating the selected outer columns.
- NOT IN is NULL-sensitive; NOT EXISTS avoids NULL contamination from unrelated inner rows.
- Optimisers may produce similar plans for IN and EXISTS; neither is universally faster.
Common mistakes candidates make
- Claiming IN always materialises its subquery or EXISTS always executes once per outer row.
- Using JOIN for an existence check without considering row multiplication.
- Treating NOT IN and NOT EXISTS as equivalent when nullable values are involved.
Likely follow-up questions
Is EXISTS faster than IN?
Not necessarily: an optimiser may translate both into similar semi-join plans. EXISTS needs only evidence of a match, but the physical work depends on the chosen plan, indexes, and data distribution.
Why can NOT IN return no rows when its subquery contains NULL?
For a nonmatching value, comparison against the NULL makes the membership result UNKNOWN, and negating UNKNOWN still gives UNKNOWN. WHERE keeps only TRUE, so those rows are excluded; use a suitable NOT EXISTS predicate or explicitly exclude NULLs where appropriate.
What is a semi-join?
A semi-join returns rows from one input that have at least one match in the other, without returning the other input’s columns or multiplying rows for multiple matches. Optimisers commonly use this operation to implement EXISTS and suitable IN subqueries.
Interview tip: Explain intent first, demonstrate one customer with multiple orders, and finish with the NULL caveat and execution-plan dependency.