title: Best way to rewrite a WHERE NOT IN clause?
body: <p>I've got a query like the one below:</p>
  
  <pre><code>Select ser.key
  From dbo.Enrlmt ser     
  Where ser.wd &gt;= @FromDate AND ser.wd &lt;= @ThrouDate AND
        ser.EnrlmtStatCode = '4321' AND
        ser.StuExitCatgCode in ('1','2','3','4','5','6','7') AND
        ser.Key not in (select Key 
                             from Enrlmt ser2 
                            where ser2.StartDate &lt;= @AsOfDate 
                              AND ser2.StartDate &gt; ser.wd 
                              AND ser2.EnrlmtStatCode = '4321')
  </code></pre>
  
  <p>That is extremely slow due to the "NOT IN" clause.  I tried rewriting it using a left outer join so that it looked like:</p>
  
  <pre><code>   Select ser.key
       From dbo.Enrlmt ser    
  LEFT JOIN dbo.Enrlmt ser2 ON ser.key = ser2.key
                           AND ser2.StartDate &lt;= @AsOfDate 
                           AND ser2.StartDate &gt; ser.wd 
                           AND ser2.EnrlmtStatCode = '4321'
     Where ser2.key is null
       AND ser.wd &gt;= @FromDate 
       AND ser.wd &lt;= @ThrouDate 
       AND ser.EnrlmtStatCode = '4321' 
       AND ser.StuExitCatgCode in ('1','2','3','4','5','6','7')
  </code></pre>
  
  <p>Which is much faster but the results don't match up.  Am i doing something wrong with this rewrite?  Is there a better way to do this?</p>