某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+—-+——-+
| Id | Name |
+—-+——-+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+—-+——-+
Orders 表：
+—-+————+
| Id | CustomerId |
+—-+————+
| 1 | 3 |
| 2 | 1 |
+—-+————+
例如给定上述表格，你的查询应返回：
+———–+
| Customers |
+———–+
| Henry |
| Max |
+———–+
answer1:
select name as Customers
from Customers
where name not in
(select Name
from Customers,Orders
WHERE Customers.id = Orders.CustomerId);错误
错误case:
输入:
{“headers”: {“Customers”: [“Id”, “Name”], “Orders”: [“Id”, “CustomerId”]},
“rows”: {“Customers”: [[1, “James”], [2, “James”]], “Orders”: [[1, 1]]}}
输出
{“headers”: [“Customers”], “values”: []}
预期结果
{“headers”:[“Customers”],”values”:[[“James”]]}
同名是真的骚。。。
name不行就用id
select name as Customers
from Customers
where Customers.id not in
(select Customers.id
from Customers,Orders
WHERE Customers.id = Orders.CustomerId);
上面这个烂sql自己写的，有子循环，且效率不高
可以使用left join
select a.Name as Customers
from Customers as a
left join Orders as b
on a.Id=b.CustomerId
where b.CustomerId is null;
其他题解，推荐浏览！