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 |
+-----------+
解法一:
使用的方法是表customers和表orders左连接,并且条件是customerId为空的客户:
SELECT NAME as Customers FROM customers as a
LEFT JOIN orders as b on a.Id = b.CustomerId
WHERE b.CustomerId is NULL;
解法二:
使用的方法是使用not in :
SELECT Name as Custmoers FROM customers as a
WHERE a.Id not in
(SELECT b.CustomerId FROM orders as b);