LeetCode 183 - Customers Who Never Order¶
Database Language: MySQL
Difficulty: 
Problem Description¶
Input¶
Table: Customers¶
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
id is the primary key (column with unique VALUES) for this table. Each row of this table indicates the ID and name of a customer.
Table: Orders¶
| Column Name | Type |
|---|---|
| id | int |
| customerId | int |
id is the primary key (column with unique VALUES) for this table.
customerId is a foreign key (reference columns) 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.
Requirement¶
Write a solution to find all customers who never order anything.
Return the result table in any order.
The result format is in the following example.
Examples¶
Example 1¶
Input¶
Customers table:
| id | name |
|---|---|
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
Orders table:
| id | customerId |
|---|---|
| 1 | 3 |
| 2 | 1 |
Output¶
| Customers |
|---|
| Henry |
| Max |
SQL Schema¶
CREATE TABLE IF NOT EXISTS Customers (id INT PRIMARY KEY, name VARCHAR(255));
CREATE TABLE IF NOT EXISTS Orders (id INT PRIMARY KEY, customerId INT);
TRUNCATE TABLE Customers;
INSERT INTO Customers (id, name) VALUES ('1', 'Joe');
INSERT INTO Customers (id, name) VALUES ('2', 'Henry');
INSERT INTO Customers (id, name) VALUES ('3', 'Sam');
INSERT INTO Customers (id, name) VALUES ('4', 'Max');
TRUNCATE TABLE Orders;
INSERT INTO Orders (id, customerId) VALUES ('1', '3');
INSERT INTO Orders (id, customerId) VALUES ('2', '1');
Solutions¶
When doing queries on 2 tables wherein the goal is to return all rows in the first table that does not exist on a second table, there are 3 possible ways of accomplishing this:
- Using
LEFT OUTER JOIN - Using
NOT EXISTS - Using
NOT IN
Solution #1 - Using LEFT OUTER JOIN¶
To identify rows in one table that does not exist on another table using the LEFT OUTER JOIN, the basic query structure is as follows:
SELECT <Output Columns>
FROM <Table 1> LEFT OUTER JOIN <Table 2>
ON <Table 1>.<Column 1> = <Table 2>.<Column 1>
WHERE <Table 2>.<Column 2> IS NULL
Using this basic query structure, to identify customers in the Customers who has never ordered anything (or does not have any rows in the Orders table based on the customerId), the 2 tables need to be joined on the customer ID column and then the result needs to be filtered out by checking if the Orders.id IS NULL. Here's how the query will look like using the LEFT OUTER JOIN:
# Final Solution 1 Query - Using LEFT OUTER JOIN
SELECT name AS Customers
FROM Customers LEFT OUTER JOIN Orders
ON Customers.id = Orders.customerId
WHERE Orders.id IS NULL
Alternatively, instead of using the IS NULL comparison operator, the <=> (NULL-safe equal) operator can be used.
# Alternative Final Solution 1 Query - Using LEFT OUTER JOIN
SELECT name AS Customers
FROM Customers LEFT OUTER JOIN Orders
ON Customers.id = Orders.customerId
WHERE Orders.id <=> NULL
Here's the corresponding query plan generated by MySQL:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | Customers | ALL | 4 | 100.00 | ||||||
| 1 | SIMPLE | Orders | ALL | 2 | 50.00 | Using where; Not exists; Using join buffer (hash join) |
And here's the fastest runtime for this query:
- Runtime: 414ms
- Beats: 99.79% as of July 15, 2024
Solution #2 - Using NOT EXISTS¶
The basic structure of using NOT EXISTS in identifying rows in one table where no row exists on another table is as follows:
SELECT <Output Columns>
FROM <Table 1>
WHERE NOT EXISTS (SELECT 'X' FROM <Table 2>
WHERE <Table 1>.<Column> = <Table 2>.<Column>)
Using this basic query structure that uses the NOT EXISTS to identify customers in the Customers table that do not have any orders in the Orders table using the customerId in the Orders to match against the id in the Customers table, the query will look as follows:
# Final Solution 2 Query - Using NOT EXISTS
SELECT name AS Customers
FROM Customers
WHERE NOT EXISTS (SELECT 'X' FROM Orders
WHERE Customers.id = Orders.customerId)
Here's the query plan generated by MySQL for this query:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | Customers | ALL | 4 | 100.00 | ||||||
| 1 | SIMPLE | Orders | ALL | 2 | 100.00 | Using where; Not exists; Using join buffer (hash join) |
And here's the fastest runtime for the NOT EXISTS solution:
- Runtime: 439ms
- Beats: 97.00% as of July 15, 2024
Solution #3 - Using NOT IN¶
The basic structure in using NOT IN in identifying rows in one table where no row exists on another table is as follows:
SELECT <Output Columns>
FROM <Table 1>
WHERE <Column 1> NOT IN (SELECT <Column> FROM <Table 2>)
Using this basic query structure that uses the NOT IN to identify customers in the Customers table that do not have any orders in the Orders table using the customerId in the Orders to match against the id in the Customers table, the query will look as follows:
# Final Solution 3 Query - Using NOT IN
SELECT name AS Customers
FROM Customers
WHERE id NOT IN (SELECT customerId FROM Orders)
Here's the query plan generated by MySQL for this query:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | Customers | ALL | 4 | 100.00 | Using where | |||||
| 2 | DEPENDENT | SUBQUERY | Orders | ALL | 2 | 75.00 |
And here's the fastest runtime for the NOT EXISTS solution:
- Runtime: 457ms
- Beats: 91.76% as of July 15, 2024
Solution Runtime Comparison¶
Here's the comparison of the fastest runtime for each of the solutions.
| Solution # | Runtime | Beats |
|---|---|---|
| 1 - Using LEFT OUTER JOIN | 414ms | 99.79% |
| 2 - Using NOT EXISTS | 439ms | 97.00% |
| 3 - Using NOT IN | 457ms | 91.76% |