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.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|