Customers Who Never Order - Problem

Write a SQL solution to find all customers who have never placed an order.

You are given two tables:

  • Customers: Contains customer information with id (primary key) and name
  • Orders: Contains order information with id (primary key) and customerId (foreign key to Customers)

Return the result table in any order showing customers who don't appear in the Orders table.

Table Schema

Customers
Column Name Type Description
id PK int Primary key - unique customer identifier
name varchar Customer name
Primary Key: id
Orders
Column Name Type Description
id PK int Primary key - unique order identifier
customerId int Foreign key reference to Customers.id
Primary Key: id

Input & Output

Example 1 — Basic Case with Multiple Customers
Input Tables:
Customers
id name
1 Joe
2 Henry
3 Sam
4 Max
Orders
id customerId
1 3
2 1
Output:
Customers
Henry
Max
💡 Note:

Joe (id=1) and Sam (id=3) have orders in the Orders table, so they are excluded. Henry (id=2) and Max (id=4) have no orders, so they are returned as customers who never order.
Example 2 — All Customers Have Orders
Input Tables:
Customers
id name
1 Alice
2 Bob
Orders
id customerId
1 1
2 2
Output:
Customers
💡 Note:

Both Alice and Bob have orders, so no customers are returned. This demonstrates the edge case where all customers have placed at least one order.
Example 3 — No Orders Exist
Input Tables:
Customers
id name
1 John
2 Jane
Orders
id customerId
Output:
Customers
John
Jane
💡 Note:

The Orders table is empty, so all customers are returned as they have never placed any orders.

Constraints

  • 1 ≤ Customers.id ≤ 1000
  • 1 ≤ Orders.id ≤ 1000
  • Orders.customerId references valid Customers.id
  • Customer names are non-empty varchar strings

Visualization

Tap to expand
Customers Who Never Order INPUT Customers Table id name 1 Joe 2 Henry 3 Sam 4 Max Orders Table id customerId 1 3 2 1 customerId references Customers.id FK: Orders.customerId ALGORITHM STEPS 1 LEFT JOIN Join Customers with Orders on id = customerId 2 Check NULL WHERE Orders.id IS NULL Finds unmatched rows 3 Alternative: NOT IN WHERE id NOT IN (SELECT customerId) 4 Select Result Return customer names with no orders SELECT name FROM Customers c LEFT JOIN Orders o WHERE o.id IS NULL FINAL RESULT Matching Process Joe (1) Has Order Henry (2) No Order! Sam (3) Has Order Max (4) No Order! Output Table Customers (name) Henry Max OK - 2 customers found Key Insight: LEFT JOIN keeps ALL customers, even without matching orders. When there's no match, the Orders columns become NULL. By filtering WHERE Orders.id IS NULL, we find customers who never placed any order. NOT IN subquery is an alternative approach. TutorialsPoint - Customers Who Never Order | Optimal Solution (LEFT JOIN / NOT IN)

Related Problems

Asked in
Amazon 28 Google 15 Microsoft 12
125.0K Views
High Frequency
~8 min Avg. Time
2.2K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen