Joins in SAP HANA

Apart from the general inner, left outer, and right outer join types, other options exist that are called referential join and text join. Below Table summarizes the different types of joins in SAP HANA.

	Result	Use	Comments
Inner	Rows where there is at least one match in both tables—if left unspecified, a join is executed as an inner join.	Attribute views and analytic views.	In the specific case where there is a filter on a field in the right table, but no field from the right table is requested, the filter isn't taken into account. The reason is that if no field of the right table is requested, the join engine skips this table for performance reasons.
Referential (default join type in the graphical interface for view definitions)	An inner join where referential integrity is assumed (see the "Comments" column).	Same as for inner join. For example, for joins between item and header data for documents (header record always exists for any item record), or for joins between different levels of the same master data, such as plant/material and material general data.	Can be faster than an inner join because the right table isn't checked when no field from the right table is requested. In that specific case, note that the join engine doesn't check the right table, and even if an entry in the right table is missing, the row from the left table is still returned. In this case, it behaves more like a left outer join. If the join must always be executed as inner, use the type Inner Join instead. (See the "Referential Join Types" box following this table.)
Left Outer	All rows from the left table, even if there is no match in the right table.	Analytic views, to join fact table as left table to attribute views as right table. Cardinality in that case should be set to N:1.	This is the join type used between the fact table and dimensions in a basic star schema (e.g., "even if no entry exists for product x in the product dimension, show all the facts in the output").
Right Outer	All rows from the right table, even if there is no match in the left table	Rarely used. Potential use is in analytic views to display all master data values even if there are no facts in the fact table for them. Possible to use in attribute views as well.	For example, when joining customer master data (right table) with sales documents (left table), this would display all customers, even the ones who did not buy anything. Typically used in the type of scenario, "Give me all customers/products/... with no sales last period," and so on.
Full	All rows from both tables, regardless of whether they match.	Rarely used. Only allowed in attribute views. Potential use is to check master data relationships.	For example, when joining customers with customer account managers, this gives all customers (even if no account manager is assigned) and all account managers (even if they have no customers assigned). May be useful to verify accuracy of master data relationships.
Text	Retrieves descriptions for codes, for example, material descriptions, and so on.	To join text tables, especially if they are language dependent. Can retrieve descriptions based on the language the user is logged in with.	Text joins can only be used in attribute views; they are not supported in analytic or calculation views.

Note:

Referential Join Types

An exception to the exception mentioned in Table 10.1 is when the referential join is executed outside of an analytic view—in which case the OLAP engine is bypassed, and only the join engine is used. It will always execute as an inner join in this case.

As a general rule, developers should be very familiar with the join types and with the few exceptions that exist for specific types in SAP HANA. A lot of detailed information is available both in SAP Help and on the SAP Community Network (SCN) website (http://scn.sap.com/welcome). The joins for the text tables are created in the same way as any other joins. You do need to go to the properties of each join and change the join type to a Text Join for these.

In text joins, you don't need to set the cardinality because it's set to 1:1 by the system. This is, strictly speaking, not correct because the text table has multiple rows for each row in the code values table (each value has descriptions in multiple languages). However, by definition of a text join, only one description will be retrieved for each code value, based on the user’s logon language. In effect, then, this becomes a 1:1 relationship.

Specifically for text joins, if the text table is language-dependent, you should always specify the Language Column property as well. This will allow you to later pass the user's logon language to the attribute view so that the texts are retrieved from the database for the correct language.