Table:
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.
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:
id | name |
1 | Joe |
2 | Henry |
3 | Sam |
4 | Max |
id | customerId |
1 | 3 |
2 | 1 |
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:力扣