Show TOC

Left Outer JoinLocate this document in the navigation structure

Use

When defining InfoSets, the objects are usually linked using inner join operators. However, you can also use left outer joins and anti-joins.

Note

Left outer joins are not possible for InfoCubes. This would have an adverse affect on performance.

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

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

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

The order of the operands is very important for a left outer join. This means that the following joins describe different results sets:

Caution

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

You should 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

    As a result of the above points it is possible to assume that a left outer join would be the best option, since it has many advantages. However, it should be made clear that a left outer join is only to be used when it is really necessary. This is because it has a significantly negative affect on performance in comparison to an inner join and is thus subject to certain restrictions (see features).

Features

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 can be defined and

  • This table in turn cannot be a right table (right operand) of a left outer join.

Tables connected with left outer joins always form the end of a chain of tables. In this way, 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 on the definition of left outer joins are due to the technical limitations of databases. These restrictions do not apply to inner joins.

Include Filter Value in Condition

In the global properties of the InfoSet, you can use an indicator to determine how a condition on a field of the left outer table is implemented in the SQL statement.

This affects the query results:

  • After you have set the indicator left outer: include filter value in the on-condition, the condition/restriction is included in the on-condition in the SQL statement. The condition is then evaluated before the join.

  • If you do not set the indicator, the condition/restriction is included in the where-condition. The condition is then only evaluated after the join.

The indicator is set to empty by default. To see the affects that this indicator has on the result, see Examples of Condition Conversion.

Example

A typical example would be a DataStore object that contains a characteristic, for example PLANT, alongside key figures in its data part. In an InfoSet, a join between this DataStore object and the characteristic PLANT is defined so that the system can access the attributes of PLANT in reporting. A query based on this DataStore object evaluates the key figures existing in the DataStore object.

If an inner join is now used and if a DataStore object record contains a value for PLANT for which there 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 (DataStore object left outer join PLANT) is used, the corresponding record is considered. However, in this case, all attributes of the (non-existent) characteristic PLANT are initial. The correct behavior depends on the type of evaluation required. Both cases are valid.

Note

The table used for selecting (the main table) may never be marked as the left outer join.

See also:

Defining Join Conditions