Customers Who Never Order - Solution & Explanation
Problem Statement
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.
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.
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 | +-----------+
Approach Overview
Problem Overview: You are given two tables: Customers and Orders. Each order references a customer through customerId. The task is to return the names of customers who never placed any order.
Approach 1: LEFT JOIN with IS NULL (O(n + m) time, O(1) extra space)
This approach performs a LEFT JOIN between the Customers table and the Orders table using Customers.Id = Orders.customerId. A left join keeps all rows from the left table and fills unmatched rows with NULL values from the right table. After the join, you filter rows where Orders.customerId IS NULL. Those rows represent customers who have no matching orders. This method is widely used in SQL queries for detecting missing relationships between tables. Query planners typically optimize joins efficiently, which makes this solution both clean and performant.
Approach 2: Subquery for Exclusion (O(n + m) time, O(n) space)
This approach filters customers using a subquery that returns all customerId values from the Orders table. The outer query selects customers whose Id does not appear in that set using NOT IN or NOT EXISTS. Conceptually, you first build a list of customers who placed orders, then exclude them from the result. In Python implementations with pandas or set operations, this translates to building a set of order customer IDs and selecting customers whose IDs are not in that set. Subquery-based filtering is common in database problems where you need to exclude rows that appear in another table.
When using NOT IN, you must ensure the subquery does not produce NULL values because SQL treats comparisons with NULL differently. Many engineers prefer NOT EXISTS because it avoids this issue and works reliably across SQL engines.
Recommended for interviews: The LEFT JOIN + IS NULL solution is the most commonly expected answer. It demonstrates a clear understanding of relational joins and how to detect unmatched rows. Interviewers often want to see that you recognize this as a classic SQL join pattern. The subquery exclusion method is also valid and shows you understand alternative query patterns, but the join-based approach is usually considered the most direct.
Approach 1: Using LEFT JOIN and IS NULL
The goal is to find customers who never made an order. By using a LEFT JOIN between the Customers and Orders tables, we can identify which customer IDs do not appear in the Orders table. We will check for NULL entries in the Orders table after the join to find these customers.
The SQL query performs a LEFT JOIN between the Customers table and the Orders table on the customer ID. A LEFT JOIN ensures that all records from the left (Customers) table are returned, along with matching records from the right (Orders) table where they exist. If there is no match, the result is NULL on the right side. We filter those results where the Orders.customerId is NULL, implying that there are no orders for those customers.
Code
SQL
Python (Using pandas)
Complexity
Time Complexity: O(n), where n is the number of rows in the Customers table.
Space Complexity: O(n), primarily for storing the result set.
Approach 2: Using Subqueries for Exclusion
This approach uses a subquery to first identify the list of customers who have placed orders. Then, it excludes these customers from the list of all customers to find those who haven't placed any orders.
This SQL solution uses a subquery to gather all customerId entries from the Orders table. The primary query selects customers from the Customers table whose id is not in the previously selected list, effectively filtering out customers who have placed an order.
Code
SQL
Python (Using set operations)
Complexity
Time Complexity: O(n + m), with n as the number of customers and m as the number of orders.
Space Complexity: O(1) for the operation itself, but the result set storage would be O(k), where k is the number of customers who have never ordered.
Approach 3: NOT IN
List all customer IDs of existing orders, and use NOT IN to find customers who are not in the list.
Approach 4: LEFT JOIN
Use LEFT JOIN to join the tables and return the data where CustomerId is NULL.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| Using LEFT JOIN and IS NULL | Time Complexity: O(n), where n is the number of rows in the Customers table. |
| Using Subqueries for Exclusion | Time Complexity: O(n + m), with n as the number of customers and m as the number of orders. |
| NOT IN | — |
| LEFT JOIN | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LEFT JOIN + IS NULL | O(n + m) | O(1) | Best general solution for SQL joins when identifying rows with no match in another table |
| Subquery with NOT IN / NOT EXISTS | O(n + m) | O(n) | Useful when filtering records based on exclusion logic or when joins are less readable |
Video Solution
LeetCode 183: Customers Who Never Order [SQL] • Frederik Müller • 13,853 views views
Watch 9 more video solutions →Frequently Asked Questions
Customers Who Never Order Python solution
Is Customers Who Never Order easy or hard?
How to solve Customers Who Never Order in O(n)?
What is the best approach for Customers Who Never Order?
Is Customers Who Never Order asked at Google/Amazon/Meta?
What data structure is used in Customers Who Never Order?
What is the time complexity of Customers Who Never Order?
Ready to solve this problem?
Practice Customers Who Never Order with our built-in code editor and test cases.
Practice on FleetCode