183. Customers Who Never Order
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 |
解法1:
NOT IN
SELECT Name AS Customers
From Customers
WHERE Id NOT IN (SELECT CustomerId FROM Orders)
解法二:
LEFT JOIN
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
SELECT Name AS Customers
FROM Customers c left join Orders o on c.Id=o.CustomerId
WHERE o.CustomerId IS NULL;