Find users who never placed an order and explain JOIN choice

WHAT THIS TESTS: This question probes whether you understand anti-join logic, how NULLs behave in SQL, and the practical differences between syntactic sugar and semantically correct patterns. The interviewer wants to see that you can retrieve absent relationships without falling into traps around three-valued logic or duplicate row inflation.

A GOOD ANSWER COVERS: A good answer writes one of two canonical patterns and explains the trade-offs. First, the LEFT JOIN approach selecting from Users left joined to Orders on user id and filtering where Orders.user_id is null. Second, the NOT EXISTS approach selecting from Users where not exists a subquery on Orders matching by user_id. The candidate should then explain why they chose one. If they pick LEFT JOIN, they should note it is easy to read but can produce duplicate rows if the Orders table has multiple rows per user, which means you may need distinct or group by. If they pick NOT EXISTS, they should highlight that it is immune to NULLs in the subquery and does not inflate counts, making it the safer default in most engines. They should also mention that NOT IN is dangerous because if Orders.user_id contains any NULL, the entire result becomes empty due to SQL three-valued logic.

COMMON WRONG ANSWERS: Common wrong answers include using an INNER JOIN with a negation, which simply cannot return non-matching rows. Another red flag is using NOT IN without acknowledging the NULL hazard, or writing a LEFT JOIN but forgetting the IS NULL filter in the WHERE clause, which effectively turns the join into an INNER JOIN. Some candidates suggest a correlated subquery with a not-equal operator, which is both inefficient and incorrect for this requirement.

LIKELY FOLLOW-UPS: The interviewer may ask how you would handle the query if the Orders table has soft-deleted rows, which would require adding Orders.deleted_at is null to the join or subquery condition. They might ask how indexes affect performance, in which case you should mention that an index on Orders.user_id makes both the JOIN and the EXISTS subquery efficient. They could also ask what happens if user_id is nullable in Orders, which would complicate the LEFT JOIN filter because NULL user_ids in Orders would falsely match the IS NULL predicate.

ONE CONCRETE EXAMPLE: Imagine Users has 10,000 rows and Orders has 50,000 rows with an average of five orders per user. A LEFT JOIN without deduplication would return 5,000 user rows, but if one user has 100 orders, that user appears 100 times in the intermediate result before filtering, which can surprise consumers of the query. In contrast, NOT EXISTS stops at the first matching order per user, so it naturally returns exactly one row per user who has never ordered, with no risk of accidental multiplication.