Version 2021

Cross Join

No PostgreSQL specific customizations were made for this feature.

Cross joins appear when two tables do not have any common key attributes between them in which they can inner join.  As a result, the two tables essentially are combined to create one table that has all the data from both tables, but this results in poorer performance with a common effect of increased execution times. Sometimes these execution times, and performance hits, can be very severe.   

The following sample query is how MicroStrategy supports cross join against PostgreSQL support. 

Copy 
select "a11"."C_NATIONKEY" AS "N_NATIONKEY", 
max("a13"."N_NAME") AS "N_NAME", 
"a11"."C_CUSTKEY" AS "C_CUSTKEY", 
max("a11"."C_NAME") AS "C_NAME", 
"a12"."R_REGIONKEY" AS "R_REGIONKEY", 
max("a12"."R_NAME") AS "R_NAME", 
"a12"."R_REGIONKEY" AS "R_REGIONKEY0", 
max("a12"."R_NAME") AS "R_NAME0", 
max("a12"."R_COMMENT") AS "R_COMMENT0", 
sum("a11"."C_ACCTBAL") AS "WJXBFS1" 
from "CUSTOMER" "a11" 
cross join "REGION" "a12" 
left outer join "NATION" "a13" 
  on  ("a11"."C_NATIONKEY" = "a13"."N_NATIONKEY" and  
"a12"."R_REGIONKEY" = "a13"."N_REGIONKEY") 
where "a11"."C_CUSTKEY" <=  5 
group by "a11"."C_NATIONKEY", 
"a11"."C_CUSTKEY", 
"a12"."R_REGIONKEY", 
"a12"."R_REGIONKEY" 

MicroStrategy provides the join type property to determines which ANSI join syntax pattern to use. 

In the MicroStrategy Very Large Database (VLDB) editor, there is a Join Type VLDB setting that has the following possible settings: 

  • Join 89

  • Join 92

  • SQL 89 Inner Join and Cross Join and SQL 92 Outer Join

  • SQL 89 Inner Join and SQL 92 Outer Join and Cross Join