183. Customers Who Never Order

Question

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

Thinking:

• Method

# Write your MySQL query statement below
select
    Name as Customers
from
    Customers
where
    Id
not in(
    select CustomerId from Orders
);

二刷

1. 还是想到使用not in连接多次查询。

   # Write your MySQL query statement below
   SELECT c.Name as Customers
   FROM Customers c
   WHERE c.id not in (
   SELECT o.CustomerId FROM Orders o
   );