LeetCode 183: Customers Who Never Order Solution in SQL Explained

Problem Statement

In LeetCode 183, you’re given two tables:

• Customers:
• id (int, primary key)
• name (varchar)
• Orders:
• id (int, primary key)
• customerId (int, foreign key to Customers.id)

Your task is to write an SQL query to return the names of customers who have never placed an order, in a result table with a single column Customers, ordered arbitrarily. This differs from duplicate detection like LeetCode 182: Duplicate Emails, focusing on absence of records across two tables.

Constraints

• Customers has at least 1 row.
• Orders may be empty.
• customerId references Customers.id.

Example

Let’s see a case:

Copy codeCustomers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Output:
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+
Explanation:
<ul>
<li>Joe (id=1) has order (customerId=1).</li>
<li>Henry (id=2) has no orders.</li>
<li>Sam (id=3) has order (customerId=3).</li>
<li>Max (id=4) has no orders.</li>
<li>Henry and Max never ordered.</li>
</ul>

This shows we’re finding customers absent from Orders.

Understanding the Problem

How do you solve LeetCode 183: Customers Who Never Order in SQL? We need to:

• Identify all customers from Customers.
• Exclude those with any orders in Orders.
• Return the name of remaining customers as Customers.

For the example, Joe and Sam have orders (customerId 1, 3), while Henry and Max do not (ids 2, 4 absent from Orders). We need a query to filter out ordering customers efficiently, not a hierarchical task like LeetCode 181: Employees Earning More Than Their Managers. We’ll use: 1. Left Join with NULL Check: Clear, efficient—our best solution. 2. Subquery with NOT IN: Alternative approach.

Let’s dive into the best solution.

Best Solution: Left Join with NULL Check Approach

Explanation

Left Join with NULL Check finds non-ordering customers by:

• Left joining Customers (c) with Orders (o) on c.id = o.customerId.
• Filtering where o.customerId is NULL (no match in Orders).
• Selecting c.name as Customers.

This ensures O(n) time with indexing on id and customerId, O(1) extra space beyond result, and clarity by leveraging the absence of matches in a left join.

For the example:

• Join: Joe→1, Henry→null, Sam→3, Max→null.
• Filter: Henry, Max (null customerIds).
• Output: "Henry", "Max".

Step-by-Step Example

Example: Customers = [1:Joe, 2:Henry, 3:Sam, 4:Max], Orders = [1:3, 2:1]

Goal: Return table with Customers = "Henry", "Max".

• Step 1: Left join Customers and Orders.
• c(1,Joe) → o(2,1): match.
• c(2,Henry) → no match: customerId=null.
• c(3,Sam) → o(1,3): match.
• c(4,Max) → no match: customerId=null.
• Step 2: Filter where o.customerId IS NULL.
• (2,Henry,null) → "Henry".
• (4,Max,null) → "Max".
• Step 3: Select name as Customers.
• Result: "Henry", "Max".
• Finish: Return ["Henry", "Max"].

How the Code Works (Left Join with NULL Check) – Detailed Line-by-Line Explanation

Here’s the SQL query with a thorough breakdown:

Copy code-- Line 1: Select customer names
SELECT 
    c.name AS Customers
    -- Name from Customers table (e.g., "Henry")

-- Line 2: Join Customers with Orders
FROM 
    Customers c
    -- Left table: all customers (e.g., Joe, Henry, Sam, Max)
LEFT JOIN 
    Orders o
    -- Right table: orders (e.g., customerId=3, 1)

-- Line 3: Match on customerId
ON 
    c.id = o.customerId
    -- Link customer to order (e.g., 1=1 for Joe, null for Henry)

-- Line 4: Filter non-ordering customers
WHERE 
    o.customerId IS NULL
    -- Keep rows with no order match (e.g., Henry, Max)

This detailed breakdown clarifies how the left join with NULL check efficiently finds non-ordering customers.

Alternative: Subquery with NOT IN Approach

Explanation

Subquery with NOT IN finds non-ordering customers by:

• Selecting name from Customers.
• Using a subquery to get all customerId values from Orders.
• Filtering where c.id NOT IN (subquery) (not present in Orders).
• Naming the result column Customers.

It’s a practical alternative, O(n²) time without indexing (O(n) with indexing), O(n) space for subquery results, but less intuitive and potentially slower than a join due to the NOT IN operation.

For the example:

• Subquery: [3, 1] (ordering customerIds).
• Filter: ids 2, 4 not in [3, 1] → "Henry", "Max".

Step-by-Step Example (Alternative)

For the same example:

• Step 1: Subquery for ordering customerIds.
• SELECT customerId FROM Orders → [3, 1].
• Step 2: Filter Customers where id NOT IN [3, 1].
• id=1 (Joe) → in → exclude.
• id=2 (Henry) → not in → "Henry".
• id=3 (Sam) → in → exclude.
• id=4 (Max) → not in → "Max".
• Step 3: Select name.
• Result: "Henry", "Max".
• Finish: Return ["Henry", "Max"].

How the Code Works (Subquery with NOT IN)

Copy codeSELECT 
    name AS Customers
FROM 
    Customers c
WHERE 
    c.id NOT IN (
        SELECT customerId 
        FROM Orders
    )

Complexity

• Left Join with NULL Check:
• Time: O(n) – join with indexing.
• Space: O(1) – minimal extra space.
• Subquery with NOT IN:
• Time: O(n²) – subquery check per row (O(n) with indexing).
• Space: O(n) – subquery result.

Efficiency Notes

Left Join with NULL Check is the best solution with O(n) time and O(1) space, offering efficiency and clarity with a single pass—Subquery with NOT IN uses O(n²) time without optimization (O(n) with indexing), requiring more space and complexity, making it a straightforward but less efficient alternative.

Key Insights

• Left Join: NULL for no match.
• NOT IN: Exclusion filter.
• CustomerId: Links tables.

Additional Example

Copy codeCustomers:
| 1 | Alice |
| 2 | Bob   |
Orders:
| 1 | 1     |
Output:
| Bob |
Explanation: Alice ordered, Bob didn’t.

Edge Cases

• Empty Orders: All customers returned.
• No Non-Orderers: Empty result.
• Single Customer, No Order: That customer returned.

Both solutions handle these well.

Final Thoughts

LeetCode 183: Customers Who Never Order in SQL is a classic join challenge. The Left Join with NULL Check solution excels with its efficiency and simplicity, while Subquery with NOT IN offers an alternative approach. Want more SQL? Try LeetCode 182: Duplicate Emails for frequency detection or explore Python Basics for coding skills. Ready to practice? Solve LeetCode 183 on LeetCode with the example tables, aiming for "Henry", "Max"—test your skills now!