Not in and not exists delivering different results

Giganews Newsgroups
Subject: Not in and not exists delivering different results
Posted by:  jpunder1 (jpunde…@hotmail.com)
Date: Fri, 25 Mar 2011

I spent several hours looking over this code, which seems straight
forward but just would not work.  I thought the first two queries
should deliver the same results, but they don't.  I am populating a
temp table with a list of values for which we do not want to report,
as they could result in duplicates.  I am then matching up against
this table to eliminate these records from my results.

The first query works fine, using a not exists and joining the tables
in a subquery.  This finds 1,040,674 rows.

The second query fails to find any rows using a simple not in, where I
would expect it to return the same results as the first.

The third query swaps IN for NOT IN, and I expected it to return
everything that is missing from the second query, but it only finds
54,639 rows.

Are the first two queries identical?  What am I missing?  I attempted
to duplicate the issue by creating and populating my own tables, but I
could not replicate this behavior.

Any advice will be much appreciated.

select count(*) from CLARITY.dbo.COVERAGE_MEM_LIST cvg
where not exists (select dup.mem_number from #Duplicates dup where
dup.mem_number = cvg.mem_number)
-- returns 1,040,674

select count(*) from CLARITY.dbo.COVERAGE_MEM_LIST cvg
where cvg.mem_number not in (select dup.mem_number from #Duplicates
dup)
-- Returns 0

select count(*) from CLARITY.dbo.COVERAGE_MEM_LIST cvg
where cvg.mem_number in (select dup.mem_number from #Duplicates dup)
-- returns 54,639

Replies