183. 从不订购的客户

发表于 更新于

难度系数： 简单
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
返回的结果表中的数据可以按任意顺序排列。

SQL结构

1
2
3
4
5
6
7
8
9
10
Create table If Not Exists Customers (id int, name varchar(255))
Create table If Not Exists Orders (id int, customerId int)
Truncate table Customers
insert into Customers (id, name) values ('1', 'Joe')
insert into Customers (id, name) values ('2', 'Henry')
insert into Customers (id, name) values ('3', 'Sam')
insert into Customers (id, name) values ('4', 'Max')
Truncate table Orders
insert into Orders (id, customerId) values ('1', '3')
insert into Orders (id, customerId) values ('2', '1')

表：Customers

1
2
3
4
5
6
7
8
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id 是这个表的主键。
表的每一行表示一个用户的ID和名字

表：Orders

1
2
3
4
5
6
7
8
9
+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
id 是这个表的主键。
customerId是来自Customers表的外键
表的每一行表示一个订单的ID以及下订单的用户的ID

示例 1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
输入：
Customers 表:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders 表:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

输出：
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解法1：

1
2
3
4
SELECT c.Name AS Customers 
FROM Customers c LEFT JOIN Orders o 
ON c.Id=o.CustomerId 
WHERE o.CustomerId IS NULL

解法2：

1
2
3
4
5
6
7
SELECT a.Name AS Customers
FROM Customers AS a
WHERE NOT EXISTS(
  SELECT *
  FROM Orders AS b
  WHERE a.Id = b.CustomerId
);

原题链接：https://leetcode.cn/problems/customers-who-never-order