Customers Who Never Order --leetCode

2019独角兽企业重金招聘Python工程师标准>>>

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

https://leetcode.com/problems/customers-who-never-order/description/

solution：

我想这个用用 left join 就行了吧
select Name as Customers from Customers c left join Orders o on c.Id = o.CustomerId
where  o.CustomerId is null;

要么关联要么就子查询 还可以写很多呀
SELECT Name as Customers  FROM Customers c WHERE c.Id NOT IN (SELECT CustomerId FROM Orders o) ;
SELECT Name as Customers  FROM Customers c WHERE 0 =  (SELECT count(1) tomerId FROM Orders o where c.Id = o.CustomerId) ;
SELECT Name as Customers  FROM Customers c WHERE NOT EXISTS (SELECT CustomerId FROM Orders o WHERE o.CustomerId = c.id) ;

git地址：https://github.com/woshiyexinjie/leetcode-xin

转载于:https://my.oschina.net/u/2277632/blog/1587438