Finding missing emails in SQL Server

Question

I am trying to do something I've done a million times and it's not working, can anyone tell me why?

I have a table for people who sent in resumes, and it has their email address in it...

I want to find out if any of these people have NOT signed up on the web site. The aspnet_Membership table has all the people who ARE signed up on the web site.

There are 9472 job seekers, with unique email addresses.

This query produces 1793 results:

select j.email from jobseeker j
join aspnet_Membership m on j.email = m.email

This suggests that there should be 7679 (9472-1793) emails of people who are not signed up on the web site. Since 1793 of them DID match, I would expect the rest of them DON'T match... but when I do the query for that, I get nothing!

Why is this query giving me nothing???

select j.email 
from jobseeker j
where j.email not in (select email from aspnet_Membership)

I don't know how that could be not working - it basically says "show me all the emails which are IN the jobseeker table, but NOT IN the aspnet_Membership table...

Answer 1

You could have a lot of duplicates out there. I'm not seeing the query error off the top of my head, but you might try writing it this way:

SELECT j.email
FROM jobseeker j
LEFT JOIN aspnet_Membership m ON m.email = j.email
WHERE m.email IS NULL

You might also throw a GROUP BY or DISTINCT in there to get rid of duplicates.

Answer 2

We had a very similar problem recently where the subquery was returning null values sometimes. Then, the in statement treats null in a weird way, I think always matching the value, so if you change your query to:

select j.email 
from jobseeker j
where j.email not in (select email from aspnet_Membership
                      where email is not null)

it may work....

Answer 3

You could use exists instead of in like this:

Select J.Email
From Jobseeker j
Where not exists (Select * From aspnetMembership a where j.email = a.email)

You should get better performance and avoid the 'weird' behaviour (which I suspect is to do with null values/results) when using in.

Answer 4

Also see Five ways to return all rows from one table which are not in another table