Alternative NOT IN ()

Question

I have a table with 14,028 rows since November 2012. I also have a table with 13,959 rows since March 2013. I use the simple NOT IN() clause to see who left:

 select * from nov_2012 where id not in(select id from mar_2013) 

This returned 396 lines, and I never thought about it until I began to analyze who left. When I pulled out all the identifiers of the lost members and put them in the temporary table ( ##lost ), 32 of them were actually still in the mar_2013 table. I can pull them out when I search for their identifiers using the following:

 select * from mar_2013 where id in(select id from ##lost) 

I can not understand what is happening. Note that the id field that I created is an IDENTITY column. Could this affect the mapping using NOT IN ? Is there a better way to check for the absence of rows between tables? I also tried:

 select a.* from nov_2012 a left join mar_2013 b on b.id = a.id where b.id is NULL 

And got the same results.

This is how I created the identification field;

 create table id_lookup( dateofcusttable date ,sin int ,sex varchar(12) ,scid int identity(777000,1)) insert into id_lookup (sin, sex) select distinct sin, sex from [Client Raw].dbo.cust20130331 where sin <> 0 order by sin, sex 

This is how I added scid to the march table:

 select scid, rowno as custrowno into scid_20130331 from [Client Raw].dbo.cust20130331 cust left join id_lookup scid on scid.sin = cust.sin and scid.sex = cust.sex update scid_20130331 set scid = custrowno where scid is NULL --for members who don't have more than one id or sin information is not available drop table Account_Part2_Current select a.*, scid into Account_Part2_Current from Account_Part1_Current a left join scid_20130331 b on b.custrowno = a.rowno_custdmd_cust 

Then I group all the information with scid

Answer 1

I would prefer this form (and here is why ):

 SELECT a.id --, other columns FROM dbo.nov_2012 AS a WHERE NOT EXISTS (SELECT 1 FROM dbo.mar_2013 WHERE id = a.id); 

However, this should still produce the same results as you, so I suspect something about a data model that you are not telling us about, for example, is mar_2013.id nullable?

Answer 2

it is logically equivalent to not and faster than not to.

 where yourfield in (select afield from somewhere minus select thesamefield where you want to exclude the record ) 

Most likely, it is not as fast as using where it does not exist, according to Aaron's answer, so you should use it if it does not exist, does not give the desired results.