CODEWITHSUNDEEP
sqlmysqljoin
timeon
timeon

Reputation: 2194

SQL statement to get all customers with no 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?

I'm using mysql.

Thank all in advance.

Upvotes: 20

Views: 110723

Answers (5)

Coo_lphotosessions
Coo_lphotosessions

Reputation: 1

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)

Upvotes: 0

Ali Maleki
Ali Maleki

Reputation: 31

You can use left join:

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

Upvotes: 1

user359040
user359040

Reputation:

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)

Upvotes: 8

Daniel Vassallo
Daniel Vassallo

Reputation: 344251

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.

Upvotes: 40

David
David

Reputation: 1927

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

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

Upvotes: 20

Related Questions