Show TOC

Union / Inner Join / Left Outer JoinLocate this document in the navigation structure

A union combines data from multiple providers and builds the union sets for the relevant data. All the values are combined.

A join combines two tables by using specific criteria. You can choose between inner join and left outer join.

The inner join combines data from both tables if all the specified criteria are met. If one or more criteria are not met, no data records are created in the result set.

The left outer join takes all the values from the left table and combines them with the values from the right table that meet the criteria. All the values from the left table are included in the result table, even if they do not meet the criteria. If the left table returns a table row and the right table returns more than one watching row, then the values of the left table are repeated for every row in the right table.

Inner Join / Left Outer Join Comparison

If a product has not been assigned to any product groups for example, this product will not be displayed with an inner join, but will with a left outer join instead.

Key Figure Multiplication in Joins

If the provider linked by join contains a provider such as a characteristic that is not contained in the providers connected by union, this could result in multiplication of key figures and therefore produce incorrect results.

For further examples, see: "What's New with SAP NetWeaver BW 7.3 and BW Accelerator 7.20" at http://www.sdn.sap.com/irj/sdn/bwa?rid=/library/uuid/70950003-f7ef-2d10-b1bc-ee483800b25cInformation published on SAP site

Example:

The InfoCubes connected by union contain characteristic Distribution Channel and key figure Sold Quantity, but do not contain characteristic Product. Characteristic Product is now added by Join. For each product, the total value for the sold quantity is now displayed in the result set, as no setting has been made to define how much should be allocated to each product. The sold quantity is thus multiplicated, and the result is incorrect.

The InfoCubes contain the following data records:

The result of the union is:

The result of the join is:

In the query, it looks as though 1 450 laptops and 450 keyboards have been sold over the distribution channel, though it was really only 450 of both products.