LEETCODE 183 - Customers Who Never Order - Solution in MySQL | LEETCODE 183 Database SQL Solution

                                                                                            LEETCODE | 183

Customers Who Never Order

LEETCODE 183 - Customers Who Never Order - Solution in MySQL | LEETCODE 183 Database SQL Solution:

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       |
+-----------+

Solution of LEETCODE 584 - Find Customer Referee:

Approach: Using sub-query and NOT IN clause [Accepted]

Algorithm:

If we have a list of customers who have ever ordered, it will be easy to know who never ordered.

We can use the following code to get such list:

select customerid from orders;

Then, we can use NOT IN to query the customers who are not in this list

MySQL:

 select customers.name as 'Customers'                               from customers                                                     where customers.id not in(                                            select customerid from orders                                );

or,

  SELECT Name as Customers from Customers                          LEFT JOIN Orders                                                  ON Customers.Id = Orders.CustomerId                              WHERE Orders.CustomerId IS NULL;

or,

select Name as Customers                                            from customers c                                                   where not exists (select * from Orders o where o.CustomerId=c.id)

Next Problem:

LEETCODE 1873 - Calculate Special Bonus - Solution in MySQL | LEETCODE 1873 Database SQL Solution

Previous Problem: 

LEETCODE 584 - Find Customer Referee - Solution in MySQL | LEETCODE 584 Database SQL Solution

Ashraf LEETCODE Solutions