Customers Who Never Order | LeetCode | MSSQL
Tags: beginners, leetcode, codenewbie, mssql
Published: 2023-05-23 16:38:00

## 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.

```sql
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.

![solution1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2f0clb5dac16wrjugha7.png)

### 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.

```sql
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.

![solution2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b50uufnajlewta3dcwnw.png)

### 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.

```sql
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.

![solution3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ovtn0hrl0lgztlao1uan.png)

### 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.

```sql
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.

![solution4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e4z4qzljklin6gs1bsto.png)

## 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](https://leetcode.com/problems/customers-who-never-order/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}