[MySQL][LeetCode][Easy] 183. Customers Who Never Order

心得

這題要show出沒有訂過東西的客戶,所以可以使用LEFT JOIN來關聯兩張表。

問題

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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

Table: Orders.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

Using the above tables as example, return the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

答案

  1. LEFT JOIN
1
2
3
4
5
6
7

# Write your MySQL query statement below
SELECT a.Name AS Customers
FROM Customers AS a
LEFT JOIN Orders AS b
ON a.Id = b.CustomerId
WHERE b.CustomerId IS NULL
  1. Sub Query
1
2
3
4
5
6
7

# Write your MySQL query statement below
SELECT a.Name AS Customers
FROM Customers AS a
WHERE ( SELECT COUNT(1)
FROM Orders AS z
WHERE z.CustomerId = a.Id) = 0