Sub Query Type

Different databases have different syntax support for sub queries. The Analytical Engine checks the Sub Query Type property for the type of syntax to use when generating a sub query.

It is more efficient to select the needed column rather than selecting every column. It is also more efficient to use the IN clause rather than using the Exists function. The most optimal option depends on the capability of your database to support different sub query syntax.

The default setting is Where col1 in (Select s1.col1...) fall back to Exists (Select *...) for multiple column IN. However, based on your database type, the default to a more optimal setting is automatically changed. See below for database platform exceptions to the default setting.

Database Exceptions to the Default Setting

Database	Default
Oracle	Where col1, col2 in (Select s1.col1, s1.col2...)
DB2 UDB	Use Temporary Table
DB2 UDB for OS/390	Where Exists (Select *...)
RedBrick	Where col1 in (Select s1.col1...) fall back to Exists (Select col1, col2...) for multiple column in

Notice that most options have a fallback action. In some scenarios, the selected option does not work, so the SQL Engine must fall back to an approach that always works. The typical scenario for falling back is when multiple columns are needed in the IN list, but the database does not support it and the correlated sub queries.

See the System Administration Guide for examples.