Whilst creating a relationship from one object to another i.e. Sales Order Header to Sales Order Item, I can determine the relationship type i.e. 1:1, 1:Many. However, I am unclear about how to specify the relationship/join type (inner/outer) and furthermore, which fields form the relationship. Is the relationship created automatically based upon the integration name? For example, the Sales Order Object may have a field with integration name ORD_NO and the item object would also have a field with name ORD_NO. Should Primary Keys that are foreign keys in another table always have the same integration name? It would be great to understand this because it's obviously incredibly important to ensure that integration names within objects do actually mean the same thing.
Lookup fields on both sides of relationship show links to related objects. You can use these fields in formulas and templates.
Please check Chapter 2 of documentation for more details.