JOIN TYPE FOR OPTIMIZING DATABASE QUERIES

A query complier analyzes a query to identify a correlated scalar subquery. The query complier transforms the query having the correlated scalar subquery into a query of AGGREGATION INNER/OUTER JOIN or MAX1ROW INNER/OUTER JOIN depending on a result type of the correlated scalar subquery. The AGGREGATION INNER/OUTER JOIN performs JOIN on the rows of the correlated scalar subquery with the rows of a main query and AGGREGATE on the joined rows and returns a result of the joined rows of the main query and aggregation value thereof. The MAX1ROW INNER/OUTER JOIN performs JOIN on the rows of the correlated scalar subquery with the rows of a main query, raises Error when the number of joined rows of the subquery is two or more and returns a result of the row of the main query and the joined row of the subquery.