题目
某网站包含两个表,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)select name as Customers from Customers c left join Orders o on c.id=o.CustomerId where o.Id is null
执行顺序:Customers,Orders,c.id=o.CustomerId,where,name
内存占用:(Customers列表字段+Orders列表字段)*Customers表大小
运行速度:设Customers行数为x,Orders行数为y,扫描行数为2x
(2)select Name as Customers from Customers where Id not in (select CustomerId from Orders)
执行顺序:Customers,ot in,Orders,CustomerId,Name
内存占用:max(Customers表大小,Orders表大小)
运行速度:设Customers行数为x,Orders行数为y,扫描行数为x*y
假设2x>x*y即(2)快,2x<x*y即(1)快。