Start of Content Area

Function documentation Left Outer Join  Locate the document in its SAP Library structure

Use

Usually, when defining InfoSets, the objects are linked via inner join operators. However, you can also use left outer joins.

Inner join and left outer join are only different in the situation where one of the involved tables does not contain any suitable record which meets the join conditions.

With an inner join (table 1 inner join table 2), no record is included in the result set in this case. However, this means that the corresponding record from tables 1 is not considered in the results set.                                       

With an left outer join (table 1 left outer join table2), exactly one record is included in the results set in this case´. In this record, the fields from table 1 contain the values of the record from table 1 and the fields from table 2 are all filled with the initial value.  

The order of the operands is very important with a left outer join. This means that the following joins describes different result sets:

This graphic is explained in the accompanying text 

Caution

The sequence must be adhered to when defining a left outer join. For an inner join, the sequence is not important.    

You can always use a left outer join when

...

       1.      it cannot be ensured that at least one suitable record is found in the involved table in accordance with the join conditions, and

       2.      you want to avoid records being included in the results set, since one of the tables returns no entry.                          

 

Recommendation

From all these considerations, we can make the conclusion that a left outer join is frequently advantageous. This is your best preference. We also want to accentuate that a left outer join is only to be used when it is really necessary. A left outer join has a significantly worse performance than a corresponding inner join and is thus subject to certain restrictions (see functions).

Functions

If a left outer join is used, the following restriction applies to the right table (right operand):

·        only join conditions with exactly one other table are allowed to be defined and

·        this table in turn is not allowed to be a right table (right operand) of a left outer join.                                        

Tables connected with left outer joins always form-figuratively speaking- the end of a chain of tables. In this ways as many tables as you want can be linked in an InfoSet with a left outer join to a core of tables that are connected using inner joins.                                                 

The restrictions made for the definition of left outer joins are due to technical limitations of the databases. These restrictions are not valid for inner joins. 

Example

A typical example would be an ODS object that contains a characteristic, for example PLANT, alongside key figures in its data part. In an InfoSet, a join between this ODS object and the characteristic PLANT is defined to enable the attributes of PLANT to be accessed with evaluations. A query based on this ODS object is to evaluate the key figures existing in the ODS object.   

If an inner join is now used and if an ODS object record contains a value for PLANT for which they is no entry in the corresponding master data table, this record is not included in the results set. Correspondingly, the key figures of this record would not be considered.  If, on the other hand, a left outer join (ODS object left outer join PLANT) is used, the affected record is considered. However, in this case, all attributes of the (non-existent) characteristic PLANT are initial. Which behavior is correct depends on the type of the evaluation required. Both cases can make sense. 

See also:

Defining Join Conditions

 

End of Content Area