Customers Who Never Order | LeetCode | MSSQL

The Problem

The task is to identify customers who have never placed an order using two tables - Customers and Orders.

The Customers table is structured as follows:

id (PK)	name
int	varchar

Each row of this table contains the ID (primary key) and name of a customer.

The Orders table is structured as follows:

id (PK)	customerId (FK)
int	int

Each row in this table contains the ID of an order and the ID of the customer who placed the order (customerId), which is a foreign key referencing the id from the Customers table.

Explanation

Consider the following example:

Customers table:

id	name
1	Joe
2	Henry
3	Sam
4	Max

Orders table:

id	customerId
1	3
2	1

The expected output is:

Customers
Henry
Max

Henry and Max are customers who never placed an order.

The Solution

We will examine four SQL solutions for this problem, each employing a different strategy. These solutions have different strengths and weaknesses concerning performance, readability, and complexity.

Source Code 1

The first solution uses a LEFT JOIN to connect the Customers and Orders tables. It then filters for cases where customerId is NULL, meaning the customer has not placed an order.

SELECT c.name AS Customers
FROM
    Customers c LEFT JOIN Orders o ON c.id = o.customerId
WHERE
    o.customerId IS NULL

The runtime for this solution is 504ms, which beats 94.29% of other submissions.

Source Code 2

The second solution first creates a CTE with distinct customerId from the Orders table. It then selects customer names not present in this CTE.

WITH id_never_order AS (
    SELECT DISTINCT customerId
    FROM Orders
)
SELECT name AS Customers
FROM
    Customers
WHERE
    id NOT IN (SELECT customerId FROM id_never_order)

The runtime for this solution is 521ms, which beats 89.80% of other submissions.

Source Code 3

The third solution follows a similar approach to solution 2 but without creating a CTE. It directly selects customer names not present in the Orders table.

SELECT name AS Customers
FROM
    Customers
WHERE
    id NOT IN (SELECT DISTINCT customerId FROM Orders)

The runtime for this solution is 920ms, which beats 9.78% of other submissions.

Source Code 4

The fourth solution uses the EXISTS clause to check if a customer has any orders. If no order exists, the customer's name is selected.

SELECT c.name AS Customers
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.customerId = c.id
)

The runtime for this solution is 668ms, which beats 50.71% of other submissions.

Conclusion

All four solutions correctly identify customers who have never placed an order. However, they vary in their performance and complexity.

Ranked by performance, from best to worst, the solutions are: Source Code 1 > Source Code 2 > Source Code 4 > Source Code 3.

You can find the original problem at LeetCode.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai