某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers 表:
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表:
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格,你的查询应返回:
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/customers-who-never-order
解题:
这是典型对两个表进行差集操作。
使用NOT IN 方法。
select customers.name as 'Customers'
from customers
where customers.id not in
(
select customerid from orders
);
使用lift join:将两个表拼接成一个表,并且通过where判断没有CustomerId 的。
SELECT Name as Customers from Customers
LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL;
官方三种解答:
SELECT A.Name from Customers A
WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId)
SELECT A.Name from Customers A
LEFT JOIN Orders B on a.Id = B.CustomerId
WHERE b.CustomerId is NULL
SELECT A.Name from Customers A
WHERE A.Id NOT IN (SELECT B.CustomerId from Orders B)
相关知识:
in或not in 和exists
或not exists,相似,只有在有null的时候情况不一样。
有个很重要的区别是,如果在子查询的结果里返回了NULL,NOT IN
子句会执行失败,因为NULL和任何值都不相等。除了这个,NOT IN
和NOT EXISTS
应该就没什么区别了,
典型例子:
select ID
from USERS
where ID in (1, 2)
and ID not in
(
select USER_ID
from EVENTS
where TYPE = 7
and USER_ID is not null
);