Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 (Français) » SQL Anywhere Server - SQL Usage » Query optimization and execution » Query execution algorithms » Types of algorithms » Miscellaneous algorithms


InList algorithm (IN)

InList is used when 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.

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.