Best join questions in October 2010

SQL statement to get all customers with no orders

Asked on Mon, 04 Oct 2010 by timeon sql mysql join
6 votes

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?

I'm using mysql.

Thank all in advance.

Answered by Daniel Vassallo

You may want to use LEFT JOIN and 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 NULL in the result set.