The Problem
The task is to identify customers who have never placed an order using two tables - Customers
and Orders
.
The Customers
table is structured as follows:
id (PK) | name |
---|---|
int | varchar |
Each row of this table contains the ID (primary key) and name of a customer.
The Orders
table is structured as follows:
id (PK) | customerId (FK) |
---|---|
int | int |
Each row in this table contains the ID of an order and the ID of the customer who placed the order (customerId
), which is a foreign key referencing the id
from the Customers
table.
Explanation
Consider the following example:
Customers table:
id | name |
---|---|
1 | Joe |
2 | Henry |
3 | Sam |
4 | Max |
Orders table:
id | customerId |
---|---|
1 | 3 |
2 | 1 |
The expected output is:
Customers |
---|
Henry |
Max |
Henry and Max are customers who never placed an order.
The Solution
We will examine four SQL solutions for this problem, each employing a different strategy. These solutions have different strengths and weaknesses concerning performance, readability, and complexity.
Source Code 1
The first solution uses a LEFT JOIN to connect the Customers
and Orders
tables. It then filters for cases where customerId
is NULL, meaning the customer has not placed an order.
SELECT c.name AS Customers
FROM
Customers c LEFT JOIN Orders o ON c.id = o.customerId
WHERE
o.customerId IS NULL
The runtime for this solution is 504ms, which beats 94.29% of other submissions.
Source Code 2
The second solution first creates a CTE with distinct customerId
from the Orders
table. It then selects customer names not present in this CTE.
WITH id_never_order AS (
SELECT DISTINCT customerId
FROM Orders
)
SELECT name AS Customers
FROM
Customers
WHERE
id NOT IN (SELECT customerId FROM id_never_order)
The runtime for this solution is 521ms, which beats 89.80% of other submissions.
Source Code 3
The third solution follows a similar approach to solution 2 but without creating a CTE. It directly selects customer names not present in the Orders
table.
SELECT name AS Customers
FROM
Customers
WHERE
id NOT IN (SELECT DISTINCT customerId FROM Orders)
The runtime for this solution is 920ms, which beats 9.78% of other submissions.
Source Code 4
The fourth solution uses the EXISTS clause to check if a customer has any orders. If no order exists, the customer's name is selected.
SELECT c.name AS Customers
FROM Customers c
WHERE NOT EXISTS (
SELECT 1 FROM Orders o WHERE o.customerId = c.id
)
The runtime for this solution is 668ms, which beats 50.71% of other submissions.
Conclusion
All four solutions correctly identify customers who have never placed an order. However, they vary in their performance and complexity.
Ranked by performance, from best to worst, the solutions are: Source Code 1 > Source Code 2 > Source Code 4 > Source Code 3.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.
Top comments (0)