Find customers who have not placed any orders

WHAT THIS TESTS: This question tests your understanding of relational algebra anti-joins in SQL. The interviewer wants to see if you know how to find rows in one table that lack corresponding rows in another. It is a fundamental pattern for cohort analysis, churn detection, and data quality checks.

A GOOD ANSWER COVERS: A good answer hits four things in order. First, write a LEFT JOIN from customers to orders on customer_id, then filter with WHERE orders.customer_id IS NULL to keep only non-ordering customers. Second, offer a NOT EXISTS correlated subquery as an alternative, which often performs better on large datasets because it can short-circuit. Third, mention that NOT IN is dangerous here because if orders.customer_id contains any NULLs, NOT IN returns an empty set, so you should avoid it unless you coalesce or filter NULLs. Fourth, note that you only need to select customers.name, so avoid selecting all columns.

COMMON WRONG ANSWERS: The most common wrong answer is an INNER JOIN between customers and orders followed by DISTINCT, which returns the exact opposite set, customers who have ordered. Another red flag is using a GROUP BY with COUNT without a HAVING clause, or suggesting a subquery with IN that again selects only ordering customers. Some candidates also forget table aliases and write ambiguous column references.

LIKELY FOLLOW-UPS: The interviewer may ask which approach is faster and why, pushing you to discuss indexes on orders.customer_id and how the query optimizer handles LEFT JOIN versus NOT EXISTS. They might also ask how the query changes if you need customers with no orders in the last twelve months, which introduces a date filter in the join or subquery. A third variant is asking for the count of such customers without returning names, which tests whether you know COUNT with a column versus COUNT with a constant.

ONE CONCRETE EXAMPLE: Here is a concrete example using LEFT JOIN. Write SELECT c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL. Here is the NOT EXISTS version. Write SELECT c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id). Both return the same result set, but the execution plan may differ depending on index coverage and table statistics.