MySQL SQL语句获取所有没有订单的客户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3859896/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL statement to get all customers with no orders
提问by timeon
I have a typical Persons table and an Orders table defined in such a way that I can do JOIN query as the following to return Orders for all Persons.
我有一个典型的 Persons 表和一个 Orders 表，以这样的方式定义，我可以按如下方式执行 JOIN 查询以返回所有人员的订单。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.id=Orders.Person_id
The question is, how do I write a statement that would return all Persons with NO Orders?
问题是，我如何编写一个语句来返回所有没有订单的人？
I'm using mysql.
我正在使用 mysql。
Thank all in advance.
提前谢谢大家。
回答by Daniel Vassallo
You may want to use LEFT JOINand IS NULL:
您可能想要使用LEFT JOIN和IS NULL：
SELECT Persons.LastName, Persons.FirstName
FROM Persons
LEFT JOIN Orders ON Persons.id = Orders.Person_id
WHERE Orders.Person_id IS NULL;
The result of a left join always contains all records of the "left" table (Persons), even if the join-condition does not find any matching record in the "right" table (Orders). When there is no match, the columns of the "right" table will NULLin the result set.
左连接的结果总是包含“左”表（人）的所有记录，即使连接条件在“右”表（订单）中没有找到任何匹配的记录。当没有匹配项时，“右”表的列将NULL出现在结果集中。
回答by David
This should work... theres more than one way to do it.
这应该有效......有不止一种方法可以做到。
select * from persons where person.id not in (select person_id from orders)
回答by David
Just for completeness, here is the not existsversion:
为了完整起见，这里是not exists版本：
select * from persons p
where not exists
(select null from orders o where o.person_id = p.id)
