183. Customers Who Never Order

SQL Schema

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

 

---

solution

# Write your MySQL query statement below

select customers.name as 'customers'
from customers
where customers.id not in
(
    select customerId from orders
)

위의 쿼리는 정답으로 표기된 쿼리이다. 서브쿼리 방식을 사용하며 109ms가 걸렸다.

내가 사용하려고 했던 쿼리는 아래의 LEFT JOIN 쿼리이다. 왼쪽 테이블의 레코드에 맞춰 오른쪽 테이블의 레코드가 join되는 방식이며, 왼쪽 쿼리의 조건에 맞지 않는 레코드는 NULL 처리한다.

 

SELECT Name AS 'Customers'
FROM Customers c
LEFT JOIN Orders o
ON c.Id = o.CustomerId
WHERE o.CustomerId IS NULL

이 방식은 128ms가 걸렸다.