InList is used in cases where an IN-list 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 do this, a join is built with a special in-list table on the left-hand side. Rows are fetched from the in-list table and used to probe the Employees table.

To use InList, each of the elements in the IN list predicate must be a constant, or a value that could be evaluated to a constant value at optimization time (such as CURRENT DATE, CURRENT TIMESTAMP, and non-deterministic system and user-defined functions), or a value that is constant within one execution of a query block (outer references). For example, the following query qualifies for InList.

SELECT *, ( 
   SELECT FIRST GivenName 
    FROM Employees e 
    WHERE e.DepartmentID IN ( 500, d.DepartmentID ) 
    ORDER BY e.DepartmentID )
FROM Departments d;

Multiple IN-list predicates can be satisfied using the same index.