How would you write a query to find customers who have orders but no payments using JOINs?
Finding Customers Who Have Orders but No Payments Using JOINs
To find customers who have placed orders but have not made any payments, you can use a LEFT JOIN between orders and payments and then filter rows where the payment does not exist (IS NULL). The key idea is: JOIN all orders, then keep only those without matching payments.
The LEFT JOIN keeps all orders even if a payment doesn't exist. Rows where payment_id is NULL indicate the order has no payment.
- 1
Use LEFT JOIN + IS NULL when you also want to fetch data from the main tables (e.g., order_id).
- 2
Use NOT EXISTS when you only need to check absence—it avoids row multiplication and can be more efficient.
- 3
Avoid using RIGHT JOIN or FULL JOIN for this; LEFT JOIN or NOT EXISTS is cleaner and more readable.
Scenario Questions
0-2 years experience
- 1We have a `customers`, `orders`, and `payments` table. Write a MySQL query that returns the IDs of customers who have placed at least one order but have no corresponding payment records.
- 2If you run a LEFT JOIN between `orders` and `payments` and see rows where `payment_id` is NULL, what does that tell you about those orders?
- 3What would happen if you used an INNER JOIN instead of a LEFT JOIN in this scenario?
2-5 years experience
- 1Our nightly report started returning customers who actually have payments. Walk me through how you would debug the query that is supposed to find customers with orders but no payments.
- 2Explain why using NOT EXISTS versus LEFT JOIN … IS NULL might give different performance on a table with millions of rows, and which you would choose.
- 3If the `payments` table can have multiple rows per order, how would you adjust the query to ensure a customer is excluded only when any payment exists for any of their orders?
5-8 years experience
- 1Design an index strategy for the customers‑orders‑payments schema to support frequent anti‑join queries like 'customers with orders but no payments' at a scale of 100 M rows.
- 2Our batch job runs this query on a sharded MySQL cluster. What changes would you make to the query or schema to avoid cross‑shard joins and keep latency low?
- 3How would you rewrite the query to be safe against future schema changes, such as adding soft‑delete columns or moving payments to a separate service?
8+ years experience
- 1We are migrating payments to an event‑sourced microservice while keeping orders in MySQL. How would you redesign the 'customers with orders but no payments' check to work across services without tight coupling?
- 2Discuss the long‑term maintenance implications of using LEFT JOIN … IS NULL versus NOT EXISTS in our codebase, considering readability, optimizer behavior, and potential future DBMS migrations.
- 3If we need to expose this anti‑join result via a GraphQL API that aggregates data from multiple databases, what architectural patterns would you recommend to keep the query performant and consistent?
Follow-up Questions
- What index would you add to make this query fast?
- How would the query change if payments could be soft‑deleted?
- Can you think of any edge cases where this query might return incorrect results?