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.
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