GuilinDev

Lc0183

05 August 2008

183 Customers Who Never Order

Suppose that a website contains two tables, the 

1

Customers
table and the 
1

Orders
table. Write a SQL query to find all customers who never order anything.

Table: 

1

Customers
.

1
2
3
4
5
6
7
8

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

Table: 

1

Orders
.

1
2
3
4
5
6

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

Using the above tables as example, return the following:

1
2
3
4
5
6

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

Schema

1
2
3
4
5
6
7
8
9
10

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

Solution

1
2

SELECT A.Name as 'Customers' from Customers A
WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId)

1
2

SELECT A.Name as 'Customers' from Customers A
WHERE A.Id NOT IN (SELECT B.CustomerId from Orders B)

1
2
3

SELECT A.Name as 'Customers' from Customers A
LEFT JOIN Orders B on  a.Id = B.CustomerId
WHERE b.CustomerId is NULL
This site is open source. Improve this page.