183. Customers Who Never Order


Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

b'
\n
\n\n
\n

Solution

\n
\n

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

\n

Algorithm

\n

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

\n

We can use the following code to get such list.

\n
select customerid from orders;\n
\n

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

\n

MySQL

\n
select customers.name as \'Customers\'\nfrom customers\nwhere customers.id not in\n(\n    select customerid from orders\n);\n
\n
'