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.