learn deep. ship safe.Practice testsFollow ByteMunSubscribe

easyJoinsSQL

Customers Who Never Ordered

Write a SQL query that lists the customers who have never placed an order: the classic anti-join asked in SQL and MySQL interviews.

SQL

You have two tables. customers(id, name) holds every customer and orders(id, customer_id, total) holds their orders, where customer_id points to customers.id.

Write a query that returns the name of every customer who has never placed an order. Return one column, name, in any order.

Examples

Example 1

Input: customers: (1, Asha), (2, Ben), (3, Chen)
orders: (10, 1, 250), (11, 3, 90)
Output: name
Ben
Why: Asha and Chen each have an order; Ben has none.

Constraints

  • Customer names are not unique: two different customers can share a name.
  • A customer can have any number of orders, including zero.
  • Return each customer without orders once.

Hints

Hint 1

Which join keeps rows from the left table even when nothing matches on the right?

Hint 2

After a LEFT JOIN, the order columns are NULL for customers with no orders.

Hint 3

NOT EXISTS is another clean way to say "has no matching order".

Solution

Show the solution (try it yourself first)

Approach: an anti-join

Join every customer to their orders with a LEFT JOIN. Customers with no orders still appear, with NULL in every order column, so filtering on o.id IS NULL keeps exactly them. NOT EXISTS expresses the same idea and is just as fast in MySQL 8, which turns both into an anti-join.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Avoid WHERE c.id NOT IN (SELECT customer_id FROM orders): if any customer_id is NULL, NOT IN returns no rows at all.

Complexity

With an index on orders.customer_id this is one pass over customers and one index lookup each: about O(n log m) for n customers and m orders. Without that index, MySQL may hash-join (8.0.18+) or scan orders repeatedly, so index foreign-key columns.

Found a mistake in this problem, its tests or the solution?

Written by Published Tests checked by Deepak
SQL · standard syntax, runs on SQLite
Case 1
Table customers
idname
1Asha
2Ben
3Chen
Table orders
idcustomer_idtotal
101250
11390
Expected (1 row)
Ben
Case 2
Table customers
idname
1Dev
2Eli
Table orders
idcustomer_idtotal
Expected (2 rows)
Dev
Eli

Run checks these 2 samples. Submit checks all 6 tests, including hidden ones.

  1. easy
  2. Highest Paid Employee in Each DepartmentJoins · GROUP BY and aggregates · Window functions · SQL
    medium
  3. Find Duplicate EmailsGROUP BY and aggregates · SQL
    easy
  4. Daily Sales Running TotalWindow functions · GROUP BY and aggregates · SQL
    medium