title: Alternative to NOT IN()
body: <p>I have a table with 14,028 rows from November 2012. I also have a table with 13,959 rows from March 2013. I am using a simple <code>NOT IN()</code> clause to see who has left:</p>
  
  <pre><code>select * from nov_2012 where id not in(select id from mar_2013)
  </code></pre>
  
  <p>This returned 396 rows and I never thought anything of it, until I went to analyze who left. When I pulled all the ids for the lost members and put them in a temp table (<code>##lost</code>), 32 of them were actually still in the <code>mar_2013</code> table. I can pull them up when I search for their ids using the following:</p>
  
  <pre><code>select * from mar_2013 where id in(select id from ##lost)
  </code></pre>
  
  <p>I can't figure out what is going on. I will mention that the <code>id</code> field I created is an <code>IDENTITY</code> column. Could that have any effect on the matching using <code>NOT IN</code>? Is there a better way to check for missing rows between tables? I have also tried:</p>
  
  <pre><code>select a.* from nov_2012 a left join mar_2013 b on b.id = a.id where b.id is NULL
  </code></pre>
  
  <p>And received the same results.</p>
  
  <p>This is how I created the identity field;</p>
  
  <pre><code>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 &lt;&gt; 0 order by sin, sex
  </code></pre>
  
  <p>This is how I added the scid into the march table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I then group all the information by the scid</p>