Customers with no orders

Hook: Interviewers love this one because it looks simple, but it reveals whether you understand how SQL finds the “missing” rows, not just the matching ones.

Question: How do you return customers who have never placed an order?

Answer: Use an ANTI-JOIN pattern, usually with LEFT JOIN plus a NULL check, or with NOT EXISTS. The idea is to keep every customer, try to match orders, and then filter to the customers where no match was found. The safest beginner-friendly version is often NOT EXISTS because it reads like plain English: “give me customers for which no related order exists.”

Interview-Ready Answer: “I’d query customers and exclude anyone who has at least one matching row in orders. In practice, I usually write NOT EXISTS because it clearly expresses the anti-join and avoids duplicates from multiple orders. A common alternative is LEFT JOIN with WHERE o.order_id IS NULL, which also works when the joined key is guaranteed non-null.”

What this is

Detailed Explanation: This is a classic “find the missing relationship” problem. In database terms, you want an anti-join, which means “rows from the left table that do not have a match in the right table.” The important mental shift is that you are not selecting orders; you are proving absence of orders.

How it works under the hood

1. Start with the full customers table. Every customer is a candidate.
2. Try to match each customer to rows in orders using customer_id.
3. If a customer has one or more matching orders, they are not part of the final answer.
4. If a customer has no match at all, keep that row.
5. Return only the customer columns you care about, usually customer_id, name, or email.

With LEFT JOIN, SQL keeps all rows from the left side and fills unmatched right-side columns with NULL. Then WHERE o.order_id IS NULL filters to only those unmatched rows. With NOT EXISTS, the database checks whether any matching order row exists for each customer and returns false when it finds one.

When to use each pattern

Why NOT IN is risky: if the subquery contains even one NULL, the comparison can become unknown for every row, and you may get no results at all. That is a favorite interview trap.

Performance notes

For most real databases, NOT EXISTS and LEFT JOIN ... IS NULL often compile into a similar anti-join plan. With an index on orders(customer_id), the database can usually find matches quickly. A realistic setup might be a customer table with 1 million rows and orders with 10 million rows; the index lets the engine avoid scanning all 10 million orders for each customer. Without an index, the query can degrade toward a nested-loop style check and become slow.

Think in rough terms: with a good index, this is often close to O(C log O) or better in practice; without one, it can behave much worse, especially on large tables. Interviewers like hearing that you care about the index on the foreign key column.

Edge cases

1. Multiple orders per customer: no problem for NOT EXISTS, but LEFT JOIN can duplicate customer rows before filtering, so make sure you filter on the right-side null correctly.
2. Nullable foreign keys: if orders.customer_id can be NULL, those rows do not match any customer. That does not change the anti-join result, but it means bad data exists.
3. Picking the wrong column: use a column from the orders table that is guaranteed non-null, such as the primary key order_id, for the null check.

Memory hook: imagine every customer carrying a “receipt card.” Orders stamp the card. The customers with no orders are the ones whose card is still blank.

Real-World Story: In an e-commerce checkout service, the product team wants a list of customers who signed up but never bought anything. Marketing uses that list for a reminder email campaign, and analytics uses it to measure conversion from signup to first purchase.

What goes wrong when this is misunderstood? A developer writes a query that joins customers to orders and filters on the wrong column, or uses NOT IN against a subquery that contains NULL. The result is either too few customers or zero customers. In production, the symptom might be a dashboard that suddenly says “0 non-buyers,” a campaign that sends no emails, or logs showing the query returned empty even though support can clearly see new users without orders. Users are affected because the business targets the wrong audience, and the team loses trust in the report.

Follow-up & Tricky Questions:

• Can you write this using NOT EXISTS instead of LEFT JOIN? Yes. It is often the cleanest expression of the anti-join because it directly states that no related row should exist.
• Why is NOT EXISTS usually safer than NOT IN? Because NOT IN can fail logically when the subquery returns NULL, while NOT EXISTS handles missing values more naturally.
• What index would you add? Add an index on orders(customer_id). That helps the database find matching orders quickly for each customer.
• What happens if a customer has multiple orders? The result should still include that customer only if they have zero orders; NOT EXISTS handles this cleanly, and LEFT JOIN needs the null filter after the join.
• How do you include customers with exactly zero orders and also show order count? Use LEFT JOIN with GROUP BY and HAVING COUNT(o.order_id) = 0, or aggregate after the anti-join.

• Gotcha: Can I check WHERE o.customer_id IS NULL? Only if you are certain the joined column cannot be null for a real order row. Safer is to check the non-null primary key, like o.order_id IS NULL.
• Gotcha: Does SELECT * matter here? It can hide duplicate columns and make review harder. In interviews, naming just the needed customer fields shows you understand the shape of the result.
• Gotcha: Is this a LEFT JOIN problem or a NOT EXISTS problem? It is really an anti-join problem. Both are valid; the best choice depends on readability and null safety.

Common Mistakes:

• Mistake: Using INNER JOIN. Correction: An inner join only keeps matches, so it removes the very customers you want to find.
• Mistake: Using NOT IN on a subquery that can return NULL. Correction: Prefer NOT EXISTS or filter nulls very carefully.
• Mistake: Checking the wrong column for NULL after a left join. Correction: Check a guaranteed non-null right-side column, usually the primary key.
• Mistake: Forgetting duplicate customers can appear before filtering. Correction: The null filter removes matches, but if you later add columns from the order table, reevaluate the logic.

Memory Hook: “Keep all customers, stamp orders, then show the blank cards.” If the card is blank, that customer has no orders.

Cheat Sheet:

• NOT EXISTS is the clearest anti-join.
• LEFT JOIN ... IS NULL is the classic alternative.
• Do not trust NOT IN if nulls are possible.
• Index orders(customer_id) for speed.
• Check a non-null order column, such as order_id.

Practice Tasks:

• Write the query first with LEFT JOIN, then rewrite it with NOT EXISTS.
• Add a customer with zero orders and verify both correct queries return them.
• Insert a NULL customer_id into orders and observe why NOT IN becomes dangerous.