Customers Who Never Order solution and explanation
Deloitte
Google
9 more videos on this problem
LeetCode 183: Customers Who Never Order [SQL]Frederik Müller · 6:08 · 13K views
Leetcode 183 - Customers Who Never Order - Python Solution | FAANG Interview - Everyday Data ScienceEveryday Data Science · 6:51 · 1K views
Customers Who Never Order | LeetCode |GeekCoders · 8:25 · 1K views
Leetcode 183 | Customers Who Never Order | FAANG SQL Left Join Interview QuestionBig Tech Mindset · 8:07 · 899 views
Leetcode 183 - Customers Who Never Order (Python and SQL) [EASY]Ryan & Matt Data Science · 8:24 · 800 views
183. Customers Who Never Order with Pandas | LeetcodeTrang Ng · 5:06 · 419 views
183. Customers Who Never Order leetcode Solution | SQL Study Plan leetcoderealNameHidden · 4:54 · 274 views
183. Customers who never Order | LeetCode | SQL | Solution | Interview ProblemData Alchemist · 3:09 · 114 views
183 Customers Who Never Order || LeetCode SQL Solutions || Leetcode SolutionKeshuEdu · 5:47 · 107 views
Customers Who Never Order | Two Solutions | SQL interview Questions | Leetcode 183Codinity · 10:33 · 98 views
The problem
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.
import pandas as pd
# Sample data
customers_data = {'id': [1, 2, 3, 4], 'name': ['Joe', 'Henry', 'Sam', 'Max']}
orders_data = {'id': [1, 2], 'customerId': [3, 1]}
# Creating DataFrame
df_customers = pd.DataFrame(customers_data)
df_orders = pd.DataFrame(orders_data)
# Performing the left join
result = pd.merge(df_customers, df_orders, left_on='id', right_on='customerId', how='left')
# Selecting customers who never ordered
never_ordered = result[result['customerId'].isnull()]['name'].tolist()
print(never_ordered) # Output: ['Henry', 'Max']SELECT Customers.name AS Customers FROM Customers LEFT JOIN Orders ON Customers.id = Orders.customerId WHERE Orders.customerId IS NULL;This solution uses Python's pandas library to replicate the SQL LEFT JOIN operation. We first create DataFrames for the customers and orders data. Then, a LEFT JOIN is performed where the left DataFrame is df_customers and we match it based on 'id' with 'customerId' of df_orders. After merging, we filter the entries where 'customerId' is NaN (indicating no matching order), and collect the corresponding customer names.
Time Complexity: O(n+m), where n is the number of customers and m is the number of orders.
Space Complexity: O(n+m) for storing the DataFrames and intermediate results.
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.
customers = {1: 'Joe', 2: 'Henry', 3: 'Sam', 4: 'Max'}
orders = {1: 3, 2: 1}
customers_set = set(customers.keys())
orders_set = set(orders.values())
never_ordered = [customers[cust_id] for cust_id in (customers_set - orders_set)]
print(never_ordered) # Output: ['Henry', 'Max']SELECT name AS Customers FROM Customers WHERE id NOT IN (SELECT customerId FROM Orders);This Python solution uses basic set operations. It initializes two sets: one with all customer IDs and another with IDs that have placed orders. By taking the difference of these sets, we identify customer IDs that never appear in orders. We then map these IDs back to their corresponding names.
Time Complexity: O(n + m), with similar notations as in SQL.
Space Complexity: O(n) for storing the sets.
Complexity comparison
| Approach | Complexity |
|---|---|
| Using LEFT JOIN and IS NULL | Time Complexity: O(n+m), where n is the number of customers and m is the number of orders. |
| Using Subqueries for Exclusion | Time Complexity: O(n + m), with similar notations as in SQL. |
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 |
Frequently asked questions
Customers Who Never Order Python solution
In Python with pandas, load both tables as DataFrames and perform a left merge on customer ID. After the merge, filter rows where the order column is null. Another option uses Python sets: collect all order customer IDs in a set and return customers whose IDs are not in that set.
Is Customers Who Never Order easy or hard?
Customers Who Never Order is considered an easy SQL problem. It tests a single concept: identifying unmatched rows using LEFT JOIN or exclusion subqueries. The challenge is recognizing the join pattern rather than implementing complex logic.
How to solve Customers Who Never Order in O(n)?
Use a LEFT JOIN between Customers and Orders and filter rows where Orders.customerId IS NULL. The database processes the join in roughly linear time relative to the combined table sizes. Alternatively, use NOT EXISTS to exclude customers appearing in the Orders table.
What is the best approach for Customers Who Never Order?
The most common solution uses a LEFT JOIN between the Customers and Orders tables and filters rows where Orders.customerId is NULL. This directly returns customers with no matching order. The query runs in O(n + m) time depending on table sizes and is the approach most interviewers expect.
Is Customers Who Never Order asked at Google/Amazon/Meta?
This problem reflects a common SQL interview pattern used by companies like Amazon, Meta, and other data-driven teams. Candidates are often asked to find records that do not exist in another table using joins or subqueries.
What data structure is used in Customers Who Never Order?
In SQL, the problem relies on relational tables and join operations rather than traditional data structures. Under the hood, database engines may use hash joins or index lookups to match rows efficiently between the Customers and Orders tables.
What is the time complexity of Customers Who Never Order?
The typical SQL solution runs in O(n + m) time where n is the number of customers and m is the number of orders. The database scans both tables and performs a join or exclusion check. Space complexity is usually O(1) for join-based solutions or O(n) if the engine materializes a subquery set.