Find Customers Who Never Order using SQL

The problem of Customers Who Never Order is a common SQL question asked in Data Science interviews. It means querying a database to identify customers who have never placed an order. So, if you want to know how to solve this problem, this article is for you. In this article, I will take you through how to find Customers Who Never Order using SQL.

Find Customers Who Never Order: Problem Statement

We have two tables: “Customers” and “Orders”. The “Customers” table contains customer information with “id” and “name” columns. The “Orders” table contains order information with the “id” and “customerId” columns, where “customerId” refers to the “id” column in the “Customers” table. Find customers who never place an order.

Sample Input and Output:

Customers:
+----+-------+
| id | name  |
+----+-------+
|  1 | Joe   |
|  2 | Henry |
|  3 | Sam   |
|  4 | Max   |
+----+-------+

Orders:
+----+------------+
| id | customerId |
+----+------------+
|  2 |          1 |
|  1 |          3 |
+----+------------+

Output:
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

Find Customers Who Never Order: Solution using SQL

To solve this problem, we need to retrieve customers who have never placed an order, which means that the output would be the customers that are in the “Customers” table but not present in the “Orders” table.

First, let’s create the input tables for our SQL database (I am using MySql):

-- Create the Customers table
CREATE TABLE Customers (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

-- Insert data into the Customers table
INSERT INTO Customers (id, name) VALUES
  (1, 'Joe'),
  (2, 'Henry'),
  (3, 'Sam'),
  (4, 'Max');

-- Create the Orders table
CREATE TABLE Orders (
  id INT PRIMARY KEY,
  customerId INT,
  FOREIGN KEY (customerId) REFERENCES Customers(id)
);

-- Insert data into the Orders table
INSERT INTO Orders (id, customerId) VALUES
  (1, 3),
  (2, 1);

Till now we have created the input tables. Now here’s how to find customers who never order using SQL:

SELECT name AS Customers
  FROM Customers
  WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE customerId = Customers.id);

Output:
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

Below is how the above solution solves the problem:

SELECT name AS Customers: This is the main query where you select the name column from the Customers table. The Customers alias is assigned to the selected column.
FROM Customers: Specifies the source table for the main query, which is the Customers table.
WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE customerId = Customers.id): This is a subquery that checks for the existence of orders for each customer. It correlates the subquery with the outer query using the condition customerId = Customers.id. The NOT EXISTS operator returns true if the subquery returns no rows, which means there are no matching orders for this customer.

Summary

To solve the Customers Who Never Order problem, we need to retrieve customers who have never placed an order, which means that the output would be the customers that are in the “Customers” table but not present in the “Orders” table. I hope you liked this article on how to find customers who never order using SQL. Feel free to ask valuable questions in the comments section below.