Joins: Finding records with no matches in another table

Table 1: clients
Table 2: orders

order to clients is a 1 to 1 relationship
clients to orders is a 1 to many (in some cases Zero)

I'm looking for a SQL statement that will give me a list of customers that have no orders.
LVL 1
djlurchAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
select * from Clients c where not exists(select 1 from Orders where Orders.ClientID = c.ClientID )
0
 
Rick_RickardsCommented:
SELECT Client.ClientID, Client.ClientName
FROM Client LEFT JOIN Orders ON Client.ClientID = Orders.ClientID
WHERE ((Orders.OrdersID) Is Null);
0
 
djlurchAuthor Commented:
Wow. I've been looking for that answer for a few years and never thought about the Is Null and didn't know about Not Exists.

Both work. I am giving points to aneesh since he answered with the first correct answer. I did try Rick's answer and it works. It is also a bit more traditional and possibly intuitive.

You guys are awesome. Thanks!
0
 
Rick_RickardsCommented:
No argument that aneeshattingal's approach is viable (for SQL server).  In the world of Access one should be wary of using Sub Queries, access just doesn't perform well with them and can in fact take hours to do what an outer join can do in seconds.  The main advantage of the outer join approach is that it works equally well in both arenas.
0
 
djlurchAuthor Commented:
Interesting. Thanks for the info.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.