# leetcode183 从不订购的客户 Customers Who Never Order

创建表和数据：

```Create table If Not Exists Customers (Idint, 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‘);```

1.顾客表的id和订单表的customerid关联，得出的是买了的东西的顾客。用left join，没买东西的顾客，其对应的订单为空。这是一种求集合差的方法。

```select C.name as Customers
from Customers as C left join Orders as O on (C.id = O.customerid)
where O.id is NULL;```

```select C.name as `Customers`
from Customers as C left join (
select distinct customerid
from Orders
) as O on (C.id = O.customerid)
where O.customerid is NULL;```

2.用not in也可以。 先用子查询将买过东西的顾客id选出来。 然后排除这些顾客的id即可。

```select C.name as Customers
from Customers as C
where C.id not in (
select distinct customerid
from Orders
) ```

EXISTS是布尔运算符，常用于测试子查询。

`SELECT select_list FROM a_table WHERE [NOT] EXISTS(subquery);`

```select C.name as `Customers`
from Customers as C
where not exists (
select distinct customerid
from Orders as O
where O.customerid = C.id
) ;```