Outer Joins

SQL Supports a large variety of join types. Only inner joins are supported in classical database views in ABAP Dictionary.

 

As a reminder, the picture above illustrates an inner join: One row of the left table and one row of the right table are combined to a common result row if they fulfill the JOIN condition.

 

 

SQL standard knows three types of outer joins.

 

 

In a left outer join, one row of the left table and one row of the right table are combined to a common result row if they fulfill the JOIN condition.

 

In addition, rows of the left table without a matching row in the right table are copied to the query result. The missing values are filled with NULL values.

 

A left outer join ensures that any entry of the left table that fulfills the selection criteria appears at least once in the join result.

 

 

In a right outer join the role of left and right table are exchanged:

 

Rows of the right table without matching row in the left table are copied to the query result. The missing values are filled with NULL values.

 

A right outer join ensures that any entry of the right table that fulfills the selection criteria appears at least once in the join result.

 

 

In a full outer join, both tables are treated in the same way.

 

Rows of both tables without matching row in the other table are copied to the query result. The missing values are filled with NULL values.

 

The figure gives you an example of a CDS view with a right outer join. The data preview for this CDS view clearly shows the entries from the right table without matching entries in the left table.

 

Cross Join

 

When two data sources are joined using CROSS JOIN, the result is the cross product of these two data sources: All entries on the left side are combined with all entries on the right side. The number of rows in the results sets is the number of rows on the left side multiplied by the number of rows on the right side. It is not possible to specify a join condition for cross joins.

 

 

 

Multiple joins

 

The FROM clause of a CDS view definition allows nested join expressions. You can use brackets in order to influence the sequence in which the system evaluates these nested expressions.

 

Without brackets, nested join expressions are evaluated in the following order:

• In the case of inner and outer joins, by the arrangement of the ON conditions. From left to right, the most adjacent ON conditions are assigned to each JOIN and this expression is bracket implicitly.
• Cross joins are evaluated from left to right.

 

 

This example reads information about ticket counters. This information is joined with details about the airport where the ticket counter is located and the carrier which runs the counter.

 

The example uses outer joins because the result set should also include airports, where there are no ticket counters, and carriers, that do not run any ticket counters at all.

 

The brackets are not required. But they are used to increase the readability of the expression.

 

The figure above shows the same nested join expression, but this time without brackets. This has no influence on the result. The two joins are evaluated in the same order. But even for experienced developers, the evaluation order will not be obvious at first glance.

 

Prerequisites and ResultSet of UNION