Suppose that a website contains two tables, the Customers
table and the Orders
table. Write a SQL query to find all customers who never order anything.
Table: Customers
.
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Table: Orders
.
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
Using the above tables as example, return the following:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
Subscribe to see which companies asked this question.
题目大意:
假设一个网站包含两个表, 顾客表Customers和订单表Orders。编写一个SQL查询找出所有从未下过订单的顾客。
解题思路:
使用NOT IN,NOT EXISTS,或者LEFT JOIN均可。
使用NOT IN
# Write your MySQL query statement below
select c1.Name as Customers from Customers c1 where c1.Id not in
(select o.CustomerId from Orders o) order by c1.Id;
使用NOT EXISTS
SELECT Name as Customers from Customers c1 where not exists
(select CustomerId from Orders o where c1.Id = o.CustomerId );
使用LEFT JOIN
select Name as Customers from Customers c1 left join Orders o on c1.Id = o.CustomerId where o.Id is NULL;