The IN List algorithm is used in cases where an IN predicate can be satisfied using an index. For example, in the following query, the optimizer recognizes that it can access the Employees table using its primary key index.
SELECT * FROM Employees WHERE EmployeeID IN ( 102, 105, 129 );
To accomplish this, a join is built with a special IN list table on the left-hand side. Rows are fetched from the IN list and used to probe the Employees table. Multiple IN lists can be satisfied using the same index. If the optimizer chooses not to use an index to satisfy the IN predicate (perhaps because another index gives better performance), then the IN List appears as a predicate in a filter.