Best way to rewrite a WHERE NOT IN clause?

Question

I have a request as shown below:

Select ser.key
From dbo.Enrlmt ser     
Where ser.wd >= @FromDate AND ser.wd <= @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 <= @AsOfDate 
                            AND ser2.StartDate > ser.wd 
                            AND ser2.EnrlmtStatCode = '4321')

This is very slow due to the "NOT IN" clause. I tried to rewrite it using the left outer join, so that it looks like this:

   Select ser.key
     From dbo.Enrlmt ser    
LEFT JOIN dbo.Enrlmt ser2 ON ser.key = ser2.key
                         AND ser2.StartDate <= @AsOfDate 
                         AND ser2.StartDate > ser.wd 
                         AND ser2.EnrlmtStatCode = '4321'
   Where ser2.key is null
     AND ser.wd >= @FromDate 
     AND ser.wd <= @ThrouDate 
     AND ser.EnrlmtStatCode = '4321' 
     AND ser.StuExitCatgCode in ('1','2','3','4','5','6','7')

This is much faster, but the results do not match. Am I rewriting something wrong with this? Is there a better way to do this?

Answer 1

This may be a typo, but in the first query you are comparing the StuKey column , while in the second query you are joining Key

, , 2 , , . .

, , , , - , , .

Answer 2

, :

ser.StuKey not in (select StuKey

:

ON ser.key = ser2.key

, :

SELECT      ser.key
FROM        dbo.Enrlmt  ser
LEFT JOIN   Enrlmt      ser2
ON          ser.StuKey         = ser2.Stukey
AND         ser.EnrlmtStatCode = ser2.EnrlmtStatCode
AND         ser2.StartDate     > ser.wd
AND         ser2.StartDate     <= @AsOfDate
WHERE       ser.wd             >= @FromDate 
AND         ser.wd             <= @ThrouDate
AND         ser.EnrlmtStatCode = '4321'
AND         ser.StuExitCatgCode in ('1','2','3','4','5','6','7')
AND         ser2.key IS NULL

( , ser2.EnrlmtStatCode = '4321' )

Answer 3

• , ser2.StartDate > ser.wd, ser.wd >= @FromDate?

:

• ser2.StartDate > ser.wd, ?
• ? : @FromDate, @ThrouDate, StartDate, wd ..

:

Select
    ser.key
From
    dbo.Enrlmt ser     
Where
    ser.wd >= @FromDate AND ser.wd <= @ThrouDate AND
    ser.EnrlmtStatCode = '4321' AND
    ser.StuExitCatgCode in ('1','2','3','4','5','6','7') AND
    not EXISTS (select *
                           from Enrlmt ser2 
                          where ser2.StartDate <= @AsOfDate 
                            AND ser2.EnrlmtStatCode = '4321'
                            AND ser2.StartDate > @FromDate --ser.wd??
                            AND ser2.Key = ser.Key)

Answer 4

This will be a good balance between efficiency and clarity:

    Select ser.key
From
    dbo.Enrlmt ser
    Left Join (select StuKey 
            from Enrlmt 
            where Enrlmt.StartDate <= @AsOfDate AND 
            Enrlmt.EnrlmtStatCode = '4321') As ser2
    ON ser.key = ser2.key And ser2.StartDate > ser.wd
Where
        ser.wd >= @FromDate AND ser.wd <= @ThrouDate AND
        ser.EnrlmtStatCode = '4321' AND
        ser.StuExitCatgCode in ('1','2','3','4','5','6','7') AND
        ser2.key Is Null

You can increase the speed by making a UDF subquery. For large volumes of records in which a subquery does a great job, consider placing all of this in a UDF or procedure and populating the temporary table with the results of the subquery using this table in the main query, and then clear the temporary table by erasing.