问题描述:
某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers
表:
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Orders
表:
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
例如给定上述表格,你的查询应返回:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
1、使用 not in
#使用 not in
SELECT c.Name as Customers from Customers c
where c.Id not in
(select distinct o.CustomerId Id from orders o );
2、使用 not exists
SELECT c.Name as Customers from Customer c
where not exists
(select distinct o.CustomersId Id from order o where c.Id = o.CustomerId);
3、使用左连接 深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接
select c.Name as Customers from Customers c
left join Orders o on c.Id = o.CustomerId
where o.CustomerId is null;