某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers
表:
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表:
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格,你的查询应返回:
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
# 498ms
select c.Name as Customers from Customers c
left join Orders o on o.CustomerId = c.Id where o.Id is null;
# 532ms
select c.Name as Customers from Customers c
where not exists (select 1 from Orders o where o.CustomerId = c.Id);
# 455ms
select c.Name as Customers from Customers c
where c.Id not in (select distinct o.CustomerId from Orders o);