Finding Data not related to relational table

[TNvol66]

I was having trouble wording my title. I'll try my best to describe my problem. I'm a SQL newbie, so go easy on me..

I'm trying to match records that "doesn't exist" between two relational tables. For instance, if I have two tables.

Table 1:

Client_ID Client_Name

Table 2:

Purchase_ID Purchase_Name CLIENT_ID

As you see, I'm trying to match records from Table 2 in which a client doesn't have a purchase.

My thoughts:

SELECT CLIENT_ID

FROM CLIENT JOIN PURCHASE USING(CLIENT_ID)

GROUP BY CLIENT_ID 

NOT HAVING PURCHASE_ID

ORDER BY CLIENT_ID DESC;

Here is some data to further more explain what I'm trying to accomplish.

Ok for instance.

Client_ID    Client_Name
1            Bob
2            Dan
3            Joe

Purchase_ID     Purchase_Name      CLIENT_ID
1               Shelf              2
2               Book               1

So, in this case I want to find what client didn't make a purchase.

Which would be Client 3, Joe.

Obviously what I've coded is wrong, I'm just looking for basic guidence on how to match records in this instance. Maybe because I'm been working for 6 hours straight and I'm overlooking, I'm not sure. Thanks in advance for your help.

[simplypixie]

In MySQL I would use:

SELECT client_id FROM client WHERE client_id NOT IN (SELECT client_id FROM purchase) ORDER BY client_id DESC

Well... What I would do, is...

Select * From Client
Where Client_ID in
( Select Client_ID From Client
  MINUS
  Select Client_ID From Purchase
);

I REALLY don't like to use "NOT in"!!!

[TNvol66]

Both look promising, but it's returning nothing for me. Very weird..

Figured it out... Works perfect! Thanks all for your help!

[simplypixie]

hfx642 - instead of just writing this

I REALLY don't like to use "NOT in"!!!

Why not explain why as well.

Efficiency!
It is much easier and quicker to find something which is IN a list,
than it is to find something which is NOT IN a list.
My method above can use indexes (if available),
while the previous method requires a full table scan.

I don't mean to sound rude, but...
If anyone starts to argue my points... I don't care!
I'm just telling you what I have observed from my 20+ years of using Oracle.

[simplypixie]

hfx642, thank you for the explanation. I didn't say you were being rude I just don't think you can say something will that without expanding on the reasons why, which you have now done.

No, No, No... I didn't mean that! lol
I did NOT take it as, you thought that I was being rude.
What I meant was, that I don't mean to sound rude (because of MY comment)
If anyone starts to argue my points... I don't care!
No offence was taken by anyone here.
It's all good!!