SQL语句可让所有客户无订单 [英] SQL statement to get all customers with no orders

问题描述

我有一个典型的Persons表和一个Orders表，其定义方式使得我可以按以下方式进行JOIN查询以返回所有Persons的Orders.

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.

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?

我正在使用mysql.

I'm using mysql.

先谢谢大家.

推荐答案

您可能要使用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;

左联接的结果始终包含左"表(人员)的所有记录，即使联接条件在右"表(订单)中找不到任何匹配的记录也是如此.如果没有匹配项，则右"表中的列将在结果集中为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 NULL in the result set.

这篇关于SQL语句可让所有客户无订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！