[SQL LEFT JOIN][Leetcode] Customer Who Never Order (풀이)

Customer Who Never Order 문제 바로가기 

 

문제 

Table: Customers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID and name of a customer.

 

Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
id is the primary key column for this table.
customerId is a foreign key of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.

 

Write an SQL query to report all customers who never order anything.

Return the result table in any order.

 

Example 1:

Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

 

---

문제 풀이

아무것도 주문하지 않은 고객의 이름을 추출하는 쿼리 입력

아무것도 주문하지 않음 = orders 테이블에 없는 고객

customers 테이블을 기준으로 orders를 LEFT JOIN 

 

SELECT customers.name AS Customers
FROM  customers
          LEFT JOIN orders ON customers.id = orders.customerID 
WHERE orders.id IS NULL  -- orders의 다른 컬럼으로 해도 동일 결과 orders.customerID IS NULL