题目描述:
某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers 表:
±—±------+
| Id | Name |
±—±------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
±—±------+
Orders 表:
±—±-----------+
| Id | CustomerId |
±—±-----------+
| 1 | 3 |
| 2 | 1 |
±—±-----------+
例如给定上述表格,你的查询应返回:
±----------+
| Customers |
±----------+
| Henry |
| Max |
±----------+
代码:
select customer.Name as Customers from Customers customer where customer.ID Not in (select CustomerID from Orders)
或
SELECT Name AS Customers FROM Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL;--从左表Customers中返回所有CustomerID匹配的行,即使右表Orders中没有匹配的行,没有匹配仍然返回但返回的是null,找到null的行即为没有在orders中的顾客。
或
select customer.Name as Customers from Customers customer
where NOT EXISTS (select * from Orders o where o.CustomerID=customer.Id)
1.where A NOT IN B:找出不在B中的A
2.where A=NULL:找出A为空,即符合要求的A不存在的情况;from T1 left join T2 on…T1为左,T2为右,返回左表所有行,即使右表中无匹配项。
3.where not NOT EXISTS(M):找出条件M不存在(没有符合条件M的记录)的情况