SQL statement to get all customers with no orders

Question

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.

Answer 1

This should work... theres more than one way to do it.

select * from persons where person.id not in (select person_id from orders)

Answer 2

Just for completeness, here is the not exists version:

select * from persons p 
where not exists
(select null from orders o where o.person_id = p.id)

Answer 3

You can use left join:

    SELECT DISTINCT o.CustomerID from Orders as o
    left join Customers as c
    on o.CustomerID=c.CustomerID