Connecting Tables (Joins)

One important activity when you create the information models is to express the relationships between different tables. Most often it is done by connecting different tables using Joins.

Following table explains different join types in SAP HANA, assuming that facts table (left table) is joined to a dimension table (right table):

Join type	Use when you want to report on	Be aware that
INNER	facts with matching dimension only	Fact without a dimension will be excluded Dimensions without a fact will be excluded
LEFT OUTER	All facts whether there is a dimension or not	Dimensions without a fact will be excluded
RIGHT OUTER	All dimensions whether there are matching facts or not	Facts without a dimension will be excluded
FULL OUTER	All facts and all dimensions
REFERENTIAL	Facts with matching dimensions only where referential integrity is ensured	The join is only executed if columns from the right table are selected in sql statement
TEXT	A multi-language table	Require a language column (SPRAS or equivalent)
TEMPORAL*	Giovanni Rovelli	Italy
SPATIAL	Giovanni Rovelli	Italy

Inner Join: It returns rows when there is matching in both sides of the join.
Left Outer Join: It returns all rows from the left table, even if there are no matches in the right table.
Right Outer Join: It returns all rows from the right table, even if there are no matches in the left table.
Full Outer Join: Full outer join combines the behaviors of left outer join and right outer join. The result set is composed of:

• Rows from tables that are matching on joined columns
• Rows from the left table with no match in the right table
• Rows from the right table with no match in the left table

Referential Join: The referential join is semantically the inner join with assumption that the left table always has a matching entry in the right table. Simply defined, referential join is an inner join to be executed with some checking conditions. These conditions are required to maintain the referential integrity between two tables:

• Join is only performed if at least one field from the right table is selected
• When field from both tables are requested an inner join is performed

In calculation views, if no fields from the right table is selected, the referential join depends on the cardinality:

• if the cardinality is 1..1 or n..1, the join is not executed. (e.g. left table contains the foreign key that points to a primary key in right table, so it makes sense the field from right table must be selected)
• the the cardinality is 1..n, the join is executed as inner join.

Text Join: Text join is used to join a text table to a master data table. Text join acts as a left outer join (left table is master data) and can be used with SAP tables where the language column (SPRAS) is present. For each attribute, it is possible to define a description mapping that will be specific to the end-user's language.