183. Customers Who Never Order (LeetCode, SQL)

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;