假设一个网站包含两个表，Customers 表和 Orders 表。编写一个SQL语句找出所有从不订购任何东西的客户。
创建表和数据：
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;
先用子查询将买过东西的顾客id选出来。 在应用left join求集合差。
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 )
集合差定义：C=A-B。C中的元素等于在A中但是不在B中。因此，对A中的每个元素a，如果元素a不在B中，则元素a就是集合C的元素。
EXISTS是布尔运算符，常用于测试子查询。
SELECT
select_list
FROM
a_table
WHERE
[NOT] EXISTS(subquery);当subquery返回任何行时，EXISTS返回true，否则返回false。
select C.name as `Customers` from Customers as C where not exists ( select distinct customerid from Orders as O where O.customerid = C.id ) ;
leetcode183 从不订购的客户 Customers Who Never Order
原文：https://www.cnblogs.com/forever-fortunate/p/11723039.html