Question

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

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')

My Interesting Code

select Name as Customers
from (
    select Customers.Name, Orders.CustomerId
    from Customers
    left join Orders on Customers.Id = Orders.CustomerId
) temp
where temp.CustomerId is null

My Perspective

For this question, firstly, you can combine the two tables, using “left join”. Then you can get a new table “temp”. If a customer does not purchase anything before, the value of “CustomerId” should be “null”. Therefore, you can select from “temp”, finding all the “null CustomerId"s. The relevant “Name"s are the results.

Also, there are two useful links, and I think they can help you. (chinese version)