Getting Primary Key Records Which Is Not Associate With Any Foreign Key

Question

I have the following two tables, affiliates and referrers.

affiliates Table

id    loginid    
3     CR0007
2     CR5604
4     VRTC0008

referrers Table

id    affiliates_id    loginid    
3     2                MLT29710
4     3                MX0001

If I want to know, an affiliate is associated with whom referrer(s), I can simply use the following query :

SELECT affiliates.loginid affiliates_loginid, referrers.loginid referrers_loginid FROM affiliates, referrers WHERE affiliates.id = referrers.affiliates_id

result Table

affiliates_loginid    referrers_loginid    
CR5604                MLT29710
CR0007                MX0001

What if, I want to know, which affiliate is not associated with any referrer. What is the single SQL statement I could use? Changing the where clause to *affiliates.id != referrers.affiliates_id* of course won't work.

My expected result is VRTC0008, as this affiliate is not associated with any referrers.

Answer 1

SELECT affiliates.loginid affiliates_loginid, referrers.loginid referrers_loginid FROM affiliates WHERE affiliates.id NOT IN (Select Distinct referrers.affiliates_id From affiliates)

Answer 2

Since your referrers table is one to many, doing a sub select will degrade in performance as the table grows.

I'm not a SQL ninja, but I would do this:

SELECT affiliates.loginid affiliates_loginid, referrers.loginid referrers_loginid 
FROM affiliates 
LEFT OUTER JOIN referrers ON(referrers.affiliates_id = affiliates.id)
WHERE referrers.affiliates_id IS NULL