Description

Table: Customers

Column NameType
idint
namevarchar
  • id is the primary key column for this table.
  • Each row of this table indicates the ID and name of a customer.

Table: Orders

Column NameType
idint
customerIdint
  • id is the primary key column for this table.
  • customerId is a foreign key of the ID from the Customers table.
  • Each row of this table indicates the ID of an order and the ID of the customer who ordered it.

Problem Statement

Write an SQL query to report all customers who never order anything. Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:

  • Customers table:
idname
1Joe
2Henry
3Sam
4Max
  • Orders table:
idcustomerId
13
21

Output:

Customers
Henry
Max

Solution

The problem is essentially asking for customers who are not present in the Orders table (i.e. those who have never ordered anything). There are two ways to solve this problem.

Approach 1: Using NOT IN Clause

You can filter customers who are present in the Orders table using WHERE clause. You need those customer names who are not in the Orders table which you can get using NOT IN clause.

1SELECT name Customers FROM Customers
2    WHERE id NOT IN (
3        SELECT customerId id
4            FROM Orders
5    );

Approach 2: Using LEFT JOIN

If you join Customers with Orders using LEFT JOIN, the customers who have never ordered anything will have their Orders.customerId as NULL. You can filter those customers using WHERE clause.

1SELECT c.name Customers FROM Customers c
2    LEFT JOIN Orders o
3    ON c.id = o.customerId
4    WHERE o.customerId IS NULL