ASA SQL User's Guide
  Query Optimization and Execution
    Query execution algorithms
      Accessing tables

IN list

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 employee table using its primary key index.

SELECT *
FROM employee
WHERE emp_id in (102, 105, 129)

In order 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 employee 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.