Version 2021

Sub Query Type

Sub Query Type is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Sub Query Type property tells the Analytical Engine what type of syntax to use when generating a subquery. A subquery is a secondary SELECT statement in the WHERE clause of the primary SQL statement.

The Sub Query Type property is database specific, due to the fact that different databases have different syntax support for subqueries. Some databases can have improved query building and performance depending on the subquery type used. For example, it is more efficient to use a subquery that only selects the needed columns rather than selecting every column. Subqueries can also be more efficient by using the IN clause rather than using the EXISTS function.

The most optimal option depends on your database capabilities. In general the default setting is WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT *...) for multiple columns IN. However, the default setting is based on the most optimal setting for your database type. See the table below for database platform exceptions to the default setting. To review example SQL syntax for each VLDB setting for Sub Query Type, see HERE EXISTS (Select *…).

Levels at Which You Can Set This

Database instance, report, and template

Database exceptions to the default setting

Database Default

DB2 UDB

Use Temporary Table, falling back to EXISTS (SELECT *...) for correlated subquery

DB2 UDB for OS/390

Where Exists (Select *...)

Microsoft Access 2000/2002/2003

Use Temporary Table, falling back to EXISTS (SELECT *...) for correlated subquery

Microsoft Excel 2000/2003

Use Temporary Table, falling back to EXISTS (SELECT *...) for correlated subquery

Netezza

Where (col1, col2...) in (Select s1.col1, s1.col2...)

Oracle

Where (col1, col2...) in (Select s1.col1, s1.col2...)

PostgreSQL

Where (col1, col2...) in (Select s1.col1, s1.col2...)

RedBrick

Where col1 in (Select s1.col1...) falling back to Exists (Select col1, col2...) for multiple column in

Teradata

Use Temporary Table, falling back to in (Select col) for correlated subquery

Notice that some 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 subqueries.

For a further discussion of the Sub Query Type VLDB property, refer to MicroStrategy Tech Note TN13870.

HERE EXISTS (Select *…)

select a31.ITEM_NBR ITEM_NBR, 
 a31.CLASS_NBR CLASS_NBR, 
 sum(a31.REG_SLS_DLR) REG_SLS_DLR 
from REGION_ITEM a31 
where (exists (select * 
 from REGION_ITEM r21, 
 LOOKUP_DAY r22 
 where r21.CUR_TRN_DT = r22.CUR_TRN_DT 
 and r22.SEASON_ID in (199501) 
 and r21.ITEM_NBR = a31.ITEM_NBR 
 and r21.CLASS_NBR = a31.CLASS_NBR)) 
group by a31.ITEM_NBR, 
 a31.CLASS_NBR 

WHERE EXISTS (SELECT col1, col2…)

select a31.ITEM_NBR ITEM_NBR, 
 a31.CLASS_NBR CLASS_NBR, 
 sum(a31.REG_SLS_DLR) REG_SLS_DLR 
from REGION_ITEM a31 
where (exists (select a31.ITEM_NBR ITEM_NBR, 
 a31.CLASS_NBR CLASS_NBR 
 from REGION_ITEM r21, 
 LOOKUP_DAY r22 
 where r21.CUR_TRN_DT = r22.CUR_TRN_DT 
 and r22.SEASON_ID in (199501) 
 and r21.ITEM_NBR = a31.ITEM_NBR 
 and r21.CLASS_NBR = a31.CLASS_NBR)) 
group by a31.ITEM_NBR, 
 a31.CLASS_NBR 

WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT * ...) for multiple columns IN

select a31.ITEM_NBR ITEM_NBR, 
 sum(a31.REG_SLS_DLR) REG_SLS_DLR 
from REGION_ITEM a31 
where ((a31.ITEM_NBR) 
 in (select r21.ITEM_NBR ITEM_NBR, 
 from REGION_ITEM r21, 
 LOOKUP_DAY r22 
 where r21.CUR_TRN_DT = r22.CUR_TRN_DT 
 and r22.SEASON_ID in (199501))) 
group by a31.ITEM_NBR 

WHERE (COL1, COL2...) IN (SELECT s1.COL1, s1.COL2...)

select a31.ITEM_NBR ITEM_NBR, 
 a31.CLASS_NBR CLASS_NBR, 
 sum(a31.REG_SLS_DLR) REG_SLS_DLR 
from REGION_ITEM a31 
where ((a31.ITEM_NBR, 
 a31.CLASS_NBR) 
 in (select r21.ITEM_NBR ITEM_NBR, 
 r21.CLASS_NBR CLASS_NBR 
 from REGION_ITEM r21, 
 LOOKUP_DAY r22 
 where r21.CUR_TRN_DT = r22.CUR_TRN_DT 
 and r22.SEASON_ID in (199501))) 
group by a31.ITEM_NBR, 
 a31.CLASS_NBR 

Use Temporary Table, falling back to EXISTS (SELECT *...) for correlated subquery (default)

create table TEMP1 as 
select r21.ITEM_NBR ITEM_NBR, 
from REGION_ITEM r21, 
 LOOKUP_DAY r22 
where r21.CUR_TRN_DT = r22.CUR_TRN_DT 
 and r22.SEASON_ID in 199501 
select a31.ITEM_NBR ITEM_NBR, 
 sum(a31.REG_SLS_DLR) REG_SLS_DLR 
from REGION_ITEM a31 
 join TEMP1 a32 
 on a31.ITEM_NBR = a32.ITEM_NBR 
group by a31.ITEM_NBR 

WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT col1, col2 ...) for multiple columns IN

select a31.ITEM_NBR ITEM_NBR, 
 sum(a31.REG_SLS_DLR) REG_SLS_DLR 
from REGION_ITEM a31 
where ((a31.ITEM_NBR) 
 in (select r21.ITEM_NBR ITEM_NBR, 
 from REGION_ITEM r21, 
 LOOKUP_DAY r22 
 where r21.CUR_TRN_DT = r22.CUR_TRN_DT 
 and r22.SEASON_ID in (199501))) 
group by a31.ITEM_NBR 

Use Temporary Table, falling back to IN (SELECT COL) for correlated subquery

create table TEMP1 as 
select r21.ITEM_NBR ITEM_NBR, 
from REGION_ITEM r21, 
 LOOKUP_DAY r22 
where r21.CUR_TRN_DT = r22.CUR_TRN_DT 
 and r22.SEASON_ID in 199501 
select a31.ITEM_NBR ITEM_NBR, 
 sum(a31.REG_SLS_DLR) REG_SLS_DLR 
from REGION_ITEM a31 
 join TEMP1 a32 
 on a31.ITEM_NBR = a32.ITEM_NBR 
group by a31.ITEM_NBR