Version 2021

Sub Query

There are many cases in which the SQL engine generates subqueries (for example, query blocks in the WHERE clause):

  1. Reports that use relationship filters

  2. Reports that use the NOT IN set qualification, such as AND NOT or AND NOT.

  3. Reports that use attribute qualification with many-to-many relationships, such as showing Revenue by Category and filtering on Catalog.

  4. Reports that raise the level of a filter, such as a dimensional metric at a region level, but qualified at a store level.

  5. Reports that use non-aggregable metrics, such as inventory metrics.

  6. Reports that use dimensional extensions.

  7. Reports that use attribute-to-attribute comparison in the filter .

The default setting for PostgreSQL and the sub-query type is option 3, where (col1,col2...) in (select s1.col1, s1 col2...). A preview is shown below.

Copy 
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

You may want to experiment with this setting to determine if a non-default setting is beneficial in your specific environment.

Option 0, WHERE EXISTS (SELECT *…), generates SQL with a sub query as shown below.

Copy 
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

Option 1, WHERE EXISTS (SELECT col1,col2…), generates SQL with a sub query as shown below.

Copy 
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