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.

Hippolytus

Answer 1

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.

Mazonson

Answer 2

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

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

Tat

This will work indeed, but can become very inefficient for large record sets as it first gets all the orders, then it filters that with the outer query. The join answer is really the best way to go about it. – Massy

WOW. This was MUCH faster than the LEFT JOIN solution - 0.08 seconds versus 22 seconds on my server! Also provided edit/copy/delete options in PHPMyAdmin, where the other did not. Thanks, David! – Rebeccarebecka

Answer 3

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)

Aero

Answer 4

You can use left join:

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

Curtice

Answer 5

Question Find customers who have never made an order. Output the first name of the customer. Data Two tables: Customers and Orders

SELECT first_name
from customers
WHERE first_name not in 
(select first_name
from customers
 join orders on  customers.id=orders.cust_id)

Mathis