18 从不订购的客户

[astak16]

题目

找出所有从不订购任何东西的客户

create table customers (
	id int primary key auto_increment,
	name varchar(255)
);
insert into customers (name) values('Joe'), ('Henry'), ('Sam'), ('Max');

create table orders (
	id int primary key auto_increment,
	customerId int
);
insert into orders (customerId) values(3),(1);

SQL：方法一

select name from customers left join orders
on customers.id = orders.customerId where isnull(customerId);

解析

使用 left join 连接 customers 和 orders 连接条件是 customers.id = orders.customersId and isnull(customersId)

SQL：方法二

select name from customers where id not in (
	select customerId from orders
);

解析

使用 not in 查出不在这些结果中的数据。

SQL：方法三

select name from customers where not exists (
	select customerId from orders where customerId = customers.id
);

解析

使用 not exists 代替 not in