> For the complete documentation index, see [llms.txt](https://joey-zhouyicheng.gitbook.io/leetcode/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://joey-zhouyicheng.gitbook.io/leetcode/51-cong-bu-ding-gou-de-ke-hu.md).

# 51 - 从不订购的客户

## 题目

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

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

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

Orders 表：

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

例如给定上述表格，你的查询应返回：

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

## 解答

### Not in

不是很理解，建表的时候为啥要加一句`if not exists` ，因为如果不加，有表会报错。加了，有表也不报错，就以为它是存在的了。

```sql
select name as 'customers' from customers where id not in (select customerid from orders)
```

> Runtime: 227 ms, faster than 94.81% of MySQL online submissions forCustomers Who Never Order.
>
> Memory Usage: N/A

官方题解似乎做麻烦了，外面的select选的只有customers这个表，好像就不用特别声明是`customer.name`

关键是`not in`，能做到反选的感觉

#### 去重

```sql
select name as 'customers' from customers where id not in (select distinct customerid from orders)
```

> Runtime: 270 ms, faster than 51.76% of MySQL online submissions forCustomers Who Never Order.
>
> Memory Usage: N/A

### left join

> 作者：jimmy00745 链接：<https://leetcode-cn.com/problems/two-sum/solution/chao-yue-guan-fang-ti-jie-li-yong-zuo-lian-jie-lai/>

```sql
select a.name as customers 
from customers as a left join orders as b on a.id=b.customerId 
where b.id is null
```

> Runtime: 258 ms, faster than 64.41% of MySQL online submissions forCustomers Who Never Order.
>
> Memory Usage: N/A

没想到on后面还能写where

估计`from`到`where`都是一组的，意思是把`a.id=b.customerId`相等的放在一起组成一张表。

![image-20190814113711240](http://ww3.sinaimg.cn/large/006tNc79gy1g5z1yx7my3j308l0473zl.jpg)

where是跟着select的，再把其中id为null的挑出来，就是最终结果了。