In this blog, I would like to share the information on Join and Key in SAP HANA.
Explanation about Join:
Join in SAP HANA is used to Join table and information view and select values as per the requirement.
A Join clause combines records from two or more tables /view in a database.
SAP HANA supports following types of join,
1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join
5. Referential Join
6. Text Join
1. The Inner Join selects the set of records that match in both the Tables.
Where to use:
Inner join should be used if referential integrity is ensured.
Inner Join is much faster that Outer Join thus is the preferred solution if Possible from semantically perspective
Attribute View: Inner Joins can be used to join different master data Tables to a joint dimension
Analytical Views: Inner Joins can be used if referential integrity cannot be ensured.
2. The Left Outer Join selects the complete set of records from first table (CUSTOMER), with the matching records (where available) in second table (SALES_ORDER). If there is no match, the right side will contain null.
3. The Right Outer Join selects the complete set of records from second table (SALES_ORDER), with the matching records (where available) in first table (CUSTOMER). If there is no match, the left side will contain null.
4. The Full Outer Join keyword selects all rows from the left table (table1) and from the right table (table2).
The Full Outer Join keyword combines the result of both LEFT OUTER and RIGHT OUTER joins.
5. Referential Join is semantically an inner join that assume that referential Integrity is given.
Note: Referential integrity is the property of database which ensures that each foreign key value in a table exists as a primary key in the referenced table. Referential join is performance wise better than inner join, but only be used when you are sure that referential integrity is maintained.
6. Text Join is used in order to get language-specific data. You have a product table that contains product IDs without descriptions and you have a text table for products that contains language-specific descriptions for each product. You can create a text join between the two tables to get the language-specific details. In a text join, the right table should be the text table and it is mandatory to specify the Language Column.
Explanation about key:
There are two types of keys
1. Primary key
2. Foreign key
Primary key:
A primary key is a field or group of fields that uniquely identify a record in a table. Primary key fields cannot be NULL and cannot contain duplicate values.
Foreign key:
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
What is a Surrogate key? How is it different from a primary key?
Surrogate key is a unique identifier or a system generated sequence number key that can act as a primary key. It can be a column or a combination of columns. Unlike a primary key, it is not picked up from the existing application data fields.
Explanation about Join:
What is SAP HANA Join?
Join in SAP HANA is used to Join table and information view and select values as per the requirement.
A Join clause combines records from two or more tables /view in a database.
SAP HANA supports following types of join,
1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join
5. Referential Join
6. Text Join
1. The Inner Join selects the set of records that match in both the Tables.
Where to use:
Inner join should be used if referential integrity is ensured.
Inner Join is much faster that Outer Join thus is the preferred solution if Possible from semantically perspective
Attribute View: Inner Joins can be used to join different master data Tables to a joint dimension
Analytical Views: Inner Joins can be used if referential integrity cannot be ensured.
2. The Left Outer Join selects the complete set of records from first table (CUSTOMER), with the matching records (where available) in second table (SALES_ORDER). If there is no match, the right side will contain null.
3. The Right Outer Join selects the complete set of records from second table (SALES_ORDER), with the matching records (where available) in first table (CUSTOMER). If there is no match, the left side will contain null.
4. The Full Outer Join keyword selects all rows from the left table (table1) and from the right table (table2).
The Full Outer Join keyword combines the result of both LEFT OUTER and RIGHT OUTER joins.
5. Referential Join is semantically an inner join that assume that referential Integrity is given.
Note: Referential integrity is the property of database which ensures that each foreign key value in a table exists as a primary key in the referenced table. Referential join is performance wise better than inner join, but only be used when you are sure that referential integrity is maintained.
6. Text Join is used in order to get language-specific data. You have a product table that contains product IDs without descriptions and you have a text table for products that contains language-specific descriptions for each product. You can create a text join between the two tables to get the language-specific details. In a text join, the right table should be the text table and it is mandatory to specify the Language Column.
There are two types of keys
1. Primary key
2. Foreign key
Primary key:
A primary key is a field or group of fields that uniquely identify a record in a table. Primary key fields cannot be NULL and cannot contain duplicate values.
Foreign key:
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
What is a Surrogate key? How is it different from a primary key?
Surrogate key is a unique identifier or a system generated sequence number key that can act as a primary key. It can be a column or a combination of columns. Unlike a primary key, it is not picked up from the existing application data fields.
No comments:
Post a Comment