[LeetCode] MySQL 183. Customers Who Never Order (LEFT JOIN 예제)

Customers Who Never Order - LeetCode

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.

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

• LEFT JOIN을 사용하여 Orders 테이블을 Customers 테이블에 JOIN시킨다.
  • id 와 customerid를 기준으로 JOIN을 해주는 대신 , id 값이 존재하지 않는 사람을 출력해야 하기 때문에 WHERE 절에 IS NULL 구문을 추가하여 결과를 출력한다.

[MySQL]

# Write your MySQL query statement below
SELECT Customers.name AS Customers
FROM Customers 
    LEFT JOIN Orders ON Customers.id = Orders.customerid
WHERE Orders.id IS NULL