183. Customers Who Never Order

Customers Who Never Order

Problem Description

SQL Schema

    Create table If Not Exists Customers (id int, name varchar(255))

    Create table If Not Exists Orders (id int, customerId int)

    Truncate table Customers

    insert into Customers (id, name) values ('1', 'Joe')

    insert into Customers (id, name) values ('2', 'Henry')

    insert into Customers (id, name) values ('3', 'Sam')

    insert into Customers (id, name) values ('4', 'Max')

    Truncate table Orders

    insert into Orders (id, customerId) values ('1', '3')

    insert into Orders (id, customerId) values ('2', '1')

Table: Customers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+

id is the primary key column 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 for this table.
customerId is a foreign key 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 an SQL query to report all customers who never order anything.

Return the result table in any order.

The query 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) Left Join

SELECT c.name  as Customers
FROM Customers c LEFT JOIN Orders o
ON c.id = o.customerId
where o.customerId is NULL;

Approach) Using Not In

SELECT c.name as Customers from Customers c
WHERE c.id not in (select o.customerId from Orders o);

Approach) Using Not In

select name as Customers from customers c
where not exists (select 1 from orders o where c.id = o.customerId)

Conclusion

That’s all folks! In this post, we solved LeetCode problem 183. Customers Who Never Order

I hope you have enjoyed this post. Feel free to share your thoughts on this.

You can find the complete source code on my GitHub repository. If you like what you learn. feel free to fork 🔪 and star ⭐ it.

In this blog, I have tried to solve leetcode questions & present the most important points to consider when improving Data structure and logic,  feel free to add, edit, comment, or ask. For more information please reach me here
Happy coding!