【LeetCode】183、Customers Who Never Order

来源：互联网发布：普通话测试模拟软件编辑：程序博客网时间：2024/06/06 07:38

题目要求：Supposethat a website contains two tables, the Customers table and the Orders table.Write a SQL query to find all customers who never order anything.

解答：1)LEFT JOIN 左联结：

SELECT NameAS Customers
FROM Customers LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE Orders.Id IS NULL

2) 组合查询，使用 NOT IN：

SELECT Name AS Customers
FROM Customers WHERE Id NOT IN
(SELECT CustomerId FROM Orders)

3) 而EXISTS 与 IN最大的区别在于 IN 引导的子句只能返回一个字段，比如：

select name from student where sex = 'm' and mark in (select 1,2,3from grade where ...)

in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。

解法:

SELECT Name AS Customers

FROM Customers WHERE NOT EXISTS

(SELECT * FROM Orders

WHERE Orders.CustomerId = Customers.Id)

阅读全文

0 0