Customers Who Never Order

Difficulty: Easy | Acceptance: 71.70% | Paid: No Topics: Database

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who have never ordered anything.

Table: Customers

id is the primary key for this table. Each row of this table indicates the ID and name of a customer.

Table: Orders

id is the primary key for this column. customerId is a foreign key to the Customers table. Each row of this table indicates the ID of an order and the ID of the customer who placed it.

The result table should contain all customers who have never ordered anything. Return the result table in any order.

• Examples
• Constraints
• Approach 1: Left Join
• Approach 2: Not In
• Approach 3: Not Exists

Examples

Example 1:

Input: Customers table:

Orders table:

Output:

Explanation: Henry and Max are the only customers who have not placed any orders.

Constraints

The Customers table may contain any number of records.
The Orders table may contain any number of records.

Approach 1: Left Join

Intuition We can join the Customers table with the Orders table using a LEFT JOIN. This ensures all customers are selected. If a customer has no matching order, the columns from the Orders table will be NULL. We then filter for these NULL values.

Steps

• Perform a LEFT JOIN on Customers and Orders where Customers.id = Orders.customerId.
• Select the name of the customers where the Orders.id is NULL.

# LeetCode 183 is a SQL problem. This function returns the SQL query string.
def query() -> str:
    return "SELECT Name as Customers FROM Customers LEFT JOIN Orders ON Customers.Id = Orders.CustomerId WHERE Orders.Id IS NULL"

Complexity

• Time: O(N + M) where N and M are the number of rows in Customers and Orders, respectively, assuming efficient indexing on join keys.
• Space: O(N) for the result set in the worst case.
• Notes: LEFT JOIN is very standard and readable for this type of exclusion.

Approach 2: Not In

Intuition We can simply select the names of customers whose id is not present in the list of customerId from the Orders table.

Steps

• Select Name from Customers.
• Filter where Id is NOT IN the subquery selecting CustomerId from Orders.

# LeetCode 183 is a SQL problem. This function returns the SQL query string.
def query() -> str:
    return "SELECT Name as Customers FROM Customers WHERE Id NOT IN (SELECT CustomerId FROM Orders)"

Complexity

• Time: O(N * M) in the worst case without proper indexing, though modern optimizers often handle this efficiently.
• Space: O(M) to store the list of CustomerIds.
• Notes: Be careful if CustomerId can be NULL in the Orders table, as NOT IN behaves unexpectedly with NULLs (though the schema implies it is a foreign key, usually non-nullable).

Approach 3: Not Exists

Intuition We use a correlated subquery with NOT EXISTS. For each customer, we check if there is no corresponding row in the Orders table.

Steps

• Select Name from Customers (aliased as c).
• Filter where NOT EXISTS (Select 1 from Orders o where o.CustomerId = c.Id).

# LeetCode 183 is a SQL problem. This function returns the SQL query string.
def query() -> str:
    return "SELECT Name as Customers FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id)"

Complexity

• Time: O(N * M) in the worst case, but often optimized to semi-joins.
• Space: O(1) auxiliary space (excluding output).
• Notes: NOT EXISTS is generally robust against NULL values compared to NOT IN.