Use a Derived Table in Place of IN Predicate With Aggregate Functions

              Using a derived table in place of the IN predicate when we are aggregating data allows us to only have to process certain table records once therefore reducing the amount of resources required to execute a query.When we use the IN predicate we first have to process the data in our subquery then we are processing a lot of the same data again (depending on the WHERE clause) in our main query. If we can use a derived table to do most of the work we can avoid the double processing of data. Before we take a look at an example to illustrate this point we'll need to add an index to our Parent table so the results are not skewed by having to do a table scan.

e.g
Let's look at a query that uses the IN predicate to return the second largest value from a table. One way to do this would be as follows.

1.SELECT MIN(IntDataColumn)

  FROM [dbo].[Parent]
 WHERE ParentID IN (SELECT TOP 2 ParentID 
                      FROM [dbo].[Parent] 
                    ORDER BY IntDataColumn DESC)

2.SELECT MIN(IntDataColumn) 

  FROM (SELECT TOP 2 IntDataColumn 
          FROM [dbo].[Parent] 
        ORDER BY IntDataColumn DESC) AS A