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

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

<p>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.</p>

<p>There are 9472 job seekers, with unique email addresses.</p>

<p>This query produces 1793 results:</p>

<pre><code>select j.email from jobseeker j
join aspnet_Membership m on j.email = m.email
</code></pre>

<p>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!</p>

<p>Why is this query giving me nothing???</p>

<pre><code>select j.email 
from jobseeker j
where j.email not in (select email from aspnet_Membership)
</code></pre>

<p>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... </p>

[{'answer_id': 57251, 'author': 'Joel Coehoorn', 'author_id': 3043, 'author_profile': 'https://Stackoverflow.com/users/3043', 'pm_score': 2, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>SELECT j.email\nFROM jobseeker j\nLEFT JOIN aspnet_Membership m ON m.email = j.email\nWHERE m.email IS NULL\n</code></pre>\n\n<p>You might also throw a GROUP BY or DISTINCT in there to get rid of duplicates.</p>\n"}, {'answer_id': 57264, 'author': 'Mike Stone', 'author_id': 122, 'author_profile': 'https://Stackoverflow.com/users/122', 'pm_score': 3, 'selected': True, 'text': '<p>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:</p>\n\n<pre><code>select j.email \nfrom jobseeker j\nwhere j.email not in (select email from aspnet_Membership\n                      where email is not null)\n</code></pre>\n\n<p>it may work....</p>\n'}, {'answer_id': 57716, 'author': 'Martynnw', 'author_id': 5466, 'author_profile': 'https://Stackoverflow.com/users/5466', 'pm_score': 0, 'selected': False, 'text': "<p>You could use <code>exists</code> instead of <code>in</code> like this:</p>\n\n<pre><code>Select J.Email\nFrom Jobseeker j\nWhere not exists (Select * From aspnetMembership a where j.email = a.email)\n</code></pre>\n\n<p>You should get better performance and avoid the 'weird' behaviour (which I suspect is to do with null values/results) when using <code>in</code>. </p>\n"}, {'answer_id': 57816, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 1, 'selected': False, 'text': '<p>Also see <a href="http://wiki.lessthandot.com/index.php/5_ways_to_return_rows_from_one_table_not_in_another_table" rel="nofollow noreferrer">Five ways to return all rows from one table which are not in another table</a> </p>\n'}]
['https://Stackoverflow.com/questions/57243', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5255/']