D8-leetcode183. 从不订购的客户(简单)

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

Orders 表：

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

准备数据：

Create table If Not Exists Customers (Id int, Name varchar(255));
Create table If Not Exists Orders (Id int, CustomerId int);
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');

insert into Orders (Id, CustomerId) values ('1', '3');
insert into Orders (Id, CustomerId) values ('2', '1');

Customers Orders

思路：
从不订购的客户-->购买次数为0
（1）左连接

select * from Customers as a left join Orders as b on a.ID=b.CustomerId;

(2)查询子查询的表CustomerId为null的Name

select Name from 
(select * from Customers as a left join Orders as b 
on a.ID=b.CustomerId) t 
where CustomerId=null;

报错：

修改代码，重新查询：

select Name from 
(select a.* ,b.CustomerId from Customers as a left join Orders as b 
on a.ID=b.CustomerId) t 
where CustomerId=null;

注意：这里的null的查询要用‘is null’而不能用‘=’,否则，就会像上面一样查询出来是空。
最终修改版：

select Name as Customers from 
(select a.* ,b.CustomerId from Customers as a left join Orders as b 
on a.ID=b.CustomerId) t 
where CustomerId  is null;

总结：

• 判空只能用is null或者is not null，不能用'='。