Write a query to find customers who have never placed an order?
Interview preparation resource from Gate Smashers.
Use a LEFT JOIN from Customers to Orders and keep rows where the joined order is NULL. Example: SELECT c.customer_id, c.customer_name FROM Customers AS c LEFT JOIN Orders AS o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;
| customer_id | customer_name | city |
|---|---|---|
| 1 | Aditi Sharma | Delhi |
| 2 | Rahul Verma | Mumbai |
| 3 | Simran Kaur | Chandigarh |
| 4 | Arjun Nair | Bengaluru |
| order_id | customer_id | amount | status |
|---|---|---|---|
| 501 | 1 | 2400 | Delivered |
| 502 | 1 | 3200 | Delivered |
| 503 | 2 | 1800 | Delivered |
| 504 | 2 | 4100 | Pending |
| 505 | 3 | 950 | Cancelled |
SELECT c.customer_id, c.customer_name
FROM Customers AS c
LEFT JOIN Orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;Direct answer (query)
Left-join every customer to Orders and retain only those rows where no order matched (order_id is NULL). The exact SQL is provided in the code block below.
Approach & rationale
A LEFT JOIN preserves all rows from the left table (Customers). When no matching Orders row exists for a customer, the Orders columns become NULL; filtering WHERE o.order_id IS NULL returns customers with no orders. It is safer to test a non-nullable key (o.order_id) rather than a business column that may itself be NULL, to avoid false positives.
- Preserve all customers: LEFT JOIN keeps every customer row regardless of matching orders.
- Detect no match: WHERE o.order_id IS NULL filters customers with no corresponding Orders row.
- Key selection: Test a non-nullable key (order_id) for NULL rather than a nullable business column.
Alternative
NOT EXISTS is an equally valid and often very clear alternative to the LEFT JOIN pattern. Use whichever form matches your team’s style or is more readable in the given context; both express the intent of finding customers without orders.
- Alternative pattern: Use NOT EXISTS with a correlated subquery as a clear alternative.
Example data (reference)
The following sample Customers and Orders tables illustrate the data used with the query. See the code block for the exact SQL.