51 - 从不订购的客户

题目

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 ，因为如果不加，有表会报错。加了，有表也不报错，就以为它是存在的了。

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，能做到反选的感觉

去重

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/

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

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