상세 컨텐츠

본문 제목

Customers Who Never Order(LeetCode)

SQL/MySQL 문제풀이

by 관재탑 2022. 7. 20. 13:31

본문

https://leetcode.com/problems/customers-who-never-order/submissions/

 

Customers Who Never Order - LeetCode

Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.

leetcode.com

 

 

문제

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.

The query result format is in the following example.

 

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       |
+-----------+

 

 

정답쿼리

SELECT Customers.name AS Customers
FROM Customers
LEFT JOIN Orders ON Customers.id = Orders.customerID
WHERE Orders.customerID IS NULL

 

 

해설

한번도 주문 안한 사람의 이름을 출력하는 쿼리므로 

LEFT조인해서 Customers테이블의 모든 데이터가 나오게 한다.

그러면 주문 안한 사람의 Orders 테이블 칼럼이 NULL값으로 나올텐데

WHERE Orders.customerID IS NULL로 주문안한 사람들만 필터링한다.

 

'SQL > MySQL 문제풀이' 카테고리의 다른 글

관련글 더보기

댓글 영역