力扣sql总结

183.从不订购的客户

某网站包含两个表，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

/* Write your T-SQL query statement below */
select c.name as Customers
from
Customers as c
where
c.id not in
(
    select distinct o.CustomerId from orders as o
)

解二：not exists

/* Write your T-SQL query statement below */
select Name as Customers
from Customers as c
where not exists
(
    select 1 from Orders as o where o.CustomerId=c.Id
 )

where not exists 下面的select 1的1 是判断子查询中where语句条件是否成立

解三：左连接

/* Write your T-SQL query statement below */
select Name as Customers
from Customers as c
left join 
Orders as o
on 
c.Id=o.CustomerId
where
o.Id is null