Customers Who Never Order

Customers Who Never Order

    Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

    Table: Customers.

    +----+-------+
    | Id | Name  |
    +----+-------+
    | 1  | Joe   |
    | 2  | Henry |
    | 3  | Sam   |
    | 4  | Max   |
    +----+-------+
    Table: Orders.

    +----+------------+
    | Id | CustomerId |
    +----+------------+
    | 1  | 3          |
    | 2  | 1          |
    +----+------------+
    Using the above tables as example, return the following:

    +-----------+
    | Customers |
    +-----------+
    | Henry     |
    | Max       |
    +-----------+

    執行左外連接，然後判斷customerid is null的記錄
    select name,customerid from Customers c left outer join Orders o on c.id=o.customerid
    +-------+------------+
    | name  | customerid |
    +-------+------------+
    | Joe   |          1 |
    | Henry |       NULL |
    | Sam   |          3 |
    | Max   |       NULL |
    +-------+------------+
    select name as Customers from (select name,customerid from Customers c left outer join Orders o on c.id=o.customerid) t where t.customerid is null;