LeetCode MySQL-183. Customers Who Never Order

    AllenLi
    LeetCode MySQL

    Write an SQL query to report all customers who never order anything.
    Return the result table in any order.

    寫一個 SQL Query來報告，所有的「customers」中沒有訂購任何東西，並回傳任意順序的結果。

    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.

    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:
    1. 選擇標題「name」為「Customers」
    2. 來自於 Customers 的 table
    3. 加入並以 Orders 的 table 為主
    4. 且 「Orders.customerId = Customers.id」
    3. 設定「不再Orders.customerId的訂單」
    (ex.LEFT JOIN 會返回左側資料表中所有資料列，如果沒有匹配的資料值，則顯示為 NULL。)
    LEFTJOIN

    Code 1:

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

    Code 2:

    SELECT name AS Customers
    FROM Customers
WHERE id NOT IN
    (SELECT customerId FROM Orders);

    newOrders

    Related Posts