In (List) Operator

IN (List) is a boolean spatial operator that returns true if at least one of the values in the list is found.

Example 1:

SELECT pop FROM table WHERE state IN ('nc', 'ny', 'ca')

If the data source provider supports IN (List), the entire query is delegated to the data source provider.

If the data source provider does not support IN (List), only part of the query is delegated:

SELECT state, pop FROM table 

Spectrum Spatial executes the entire query, using the results from the data provider:

SELECT pop FROM <results from the delegation> WHERE state IN ('nc', 'ny', 'ca')

Example 2:

SELECT pop FROM table WHERE LOWER(state) IN ('nc', 'ny', 'ca')

This query violates the rule that says functions, in this case LOWER(), and operators in a non-aggregating query cannot be delegated to the data provider. The data source provider, then, will provide the raw returns from this query:

SELECT state, pop FROM table 

Spectrum Spatial executes the entire query, using the results from the data source provider:

SELECT pop FROM <results from the delegation> WHERE LOWER(state) IN ('nc', 'ny', 'ca')

Example 3:

SELECT pop FROM table WHERE state IN ('ny')

If the data source provider does not support IN with a single argument in the list, it cannot be delegated. It is converted to an '=' operator: See Attribute Comparison Operators. The data source provider must support '=' for the query to be delegated.

SELECT pop FROM table WHERE state = 'ny'