Join Type

The Join Type property determines which ANSI join syntax pattern to use. Some databases, such as Oracle, do not support the ANSI 92 standard yet. Some databases, such as DB2, support both Join 89 and Join 92. Other databases, such as some versions of Teradata, have a mix of the join standards and therefore need their own setting.

MicroStrategy uses different defaults for the join type based on the database you are using. This is to support the most common scenarios for your databases. When selecting a different join type than the default, it is recommended to test this with a report rather than the entire database. By using this strategy you can determine if the join type functions correctly for your database while also providing the required performance.

If the Full Outer Join Support VLDB property (see Join Type) is set to Support, this property is ignored and the Join 92 standard is used.

Levels at Which You Can Set This

Database instance, report, and template

Examples

Join 89 (default)

select a22.STORE_NBR STORE_NBR, 

max(a22.STORE_DESC) STORE_DESC, 

a21.CUR_TRN_DT CUR_TRN_DT, 

sum(a21.REG_SLS_DLR) WJXBFS1 

from STORE_DIVISION a21, 

LOOKUP_STORE a22 

where a21.STORE_NBR = a22.STORE_NBR 

group by a22.STORE_NBR, 

a21.CUR_TRN_DT 

Join 92

select a21.CUR_TRN_DT CUR_TRN_DT, 

a22.STORE_NBR STORE_NBR, 

max(a22.STORE_DESC) STORE_DESC, 

sum(a21.REG_SLS_DLR) WJXBFS1 

from STORE_DIVISION a21 

join LOOKUP_STORE a22 

on (a21.STORE_NBR = a22.STORE_NBR) 

group by a21.CUR_TRN_DT, 

a22.STORE_NBR 

SQL 89 Inner Join and SQL 92 Outer Join

create table ZZOL00 as 

select a23.STORE_NBR STORE_NBR, 

a23.MARKET_NBR MARKET_NBR, 

a22.DEPARTMENT_NBR DEPARTMENT_NBR, 

a21.CUR_TRN_DT CUR_TRN_DT 

from LOOKUP_DAY a21, 

LOOKUP_DEPARTMENT a22, 

LOOKUP_STORE a23 

select a21.MARKET_NBR MARKET_NBR, 

max(a24.MARKET_DESC) MARKET_DESC, 

sum((a22.COST_AMT * a23.TOT_SLS_DLR)) SUMTSC 

from ZZOL00 a21 

left outer join COST_STORE_DEP a22 

on (a21.DEPARTMENT_NBR = a22.DEPARTMENT_NBR and 

a21.CUR_TRN_DT = a22.CUR_TRN_DT and 

a21.STORE_NBR = a22.STORE_NBR) 

left outer join STORE_DEPARTMENT a23 

on (a21.STORE_NBR = a23.STORE_NBR and 

a21.DEPARTMENT_NBR = a23.DEPARTMENT_NBR and 

a21.CUR_TRN_DT = a23.CUR_TRN_DT), 

LOOKUP_MARKET a24 

where a21.MARKET_NBR = a24.MARKET_NBR 

group by a21.MARKET_NBR 

SQL 89 Inner Join and SQL 92 Outer & Cross

create table ZZOL00 as 

select a23.STORE_NBR STORE_NBR, 

a23.MARKET_NBR MARKET_NBR, 

a22.DEPARTMENT_NBR DEPARTMENT_NBR, 

a21.CUR_TRN_DT CUR_TRN_DT 

from LOOKUP_DAY a21 

cross join LOOKUP_DEPARTMENT a22 

cross join LOOKUP_STORE a23 

select a21.MARKET_NBR MARKET_NBR, 

max(a24.MARKET_DESC) MARKET_DESC, 

sum((a22.COST_AMT * a23.TOT_SLS_DLR)) SUMTSC 

from ZZOL00 a21 

left outer join COST_STORE_DEP a22 

on (a21.DEPARTMENT_NBR = a22.DEPARTMENT_NBR

and 

a21.CUR_TRN_DT = a22.CUR_TRN_DT and 

a21.STORE_NBR = a22.STORE_NBR) 

left outer join STORE_DEPARTMENT a23 

on (a21.STORE_NBR = a23.STORE_NBR and 

a21.DEPARTMENT_NBR = a23.DEPARTMENT_NBR and 

a21.CUR_TRN_DT = a23.CUR_TRN_DT), 

LOOKUP_MARKET a24 

where a21.MARKET_NBR = a24.MARKET_NBR 

group by a21.MARKET_NBR