LeetCode从不订购的客户SQL

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+

例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+

解法一：

# Write your MySQL query statement below
select Name as Customers from Customers where Id not in (select CustomerId from Orders)

解法二：比解法一效率高

# Write your MySQL query statement below
select Name as Customers from Customers where not exists (select CustomerId from Orders where Orders.CustomerId = Customers.Id)

备注：

not in 存在隐患，如果查询的 CustomerId 为 null 时，就会查询结果错误。

not exists 可避免 not in 上面问题

解法三：连接

# Write your MySQL query statement below
select Name as Customers from Customers as a left join Orders as b on a.Id = b.CustomerId where b.Id is null

做连接后表如下，然后做筛选。便于理解。

Id	Name	Id	CustomerId
1	Joe	2	1
2	Henry	null	null
3	Sam	1	3
4	Max	null	null