Leetcode # 183 Customers Who Never Order

Table:

Customers

Column Name	Type
id	int
name	varchar

Create table If Not Exists Customer (id int, name varchar(255));
Create table If Not Exists Orders (id int, customerId int);
Truncate table Customer;
insert into Customer (id, name) values ('1', 'Joe');
insert into Customer (id, name) values ('2', 'Henry');
insert into Customer (id, name) values ('3', 'Sam');
insert into Customer (id, name) values ('4', 'Max');
Truncate table Orders;
insert into Orders (id, customerId) values ('1', '3');
insert into Orders (id, customerId) values ('2', '1');

id is the primary key column for this table.
Each row of this table indicates the ID and name of a customer.

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.
 

Require:

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:

Customers table

id	name
1	Joe
2	Henry
3	Sam
4	Max

Orders

id	customerId
1	3
2	1

Output

Customers

Henry

Max

SQL:

-- 本题注意 需要将column :name 改名为Customers

此外关键字为NOT in

逻辑是现将已经购买的商品的人筛出来然后使用关键函数NOT in把所选出来的筛掉就可返回正确结果.

SELECT customerid FROM orders;-- not in is satifor
SELECT name as Customers from Customer WHERE id NOT in (
SELECT customerid FROM orders);

用join方法实现的如下:

本解法中使用连接将没有购买的人筛出Null值然后使用is null函数 即可返回正确结果(cited by 猴子)

select a.Name as Customers
from Customers as a
left join Orders as b
on a.Id=b.CustomerId
where b.CustomerId is null;

Reference:力扣