When you have three or more tables that require outer joins, the order in which the tables are joined is critical. Consider the example below:
In the sample preview below, the entries, 1, 2 ,3, and 4, in Table4 are taken and outer-joined with the records in the two other tables. The three other tables contain fewer records. The relationships are defined but now the order of execution critical. Relationship A is executed first, followed by B, then C.
Below is the query that is generated:
The nested join syntax that is generated forces the order of execution:
- Join Table1 and Table2 (shown in red)
- Join Table3 and A = B (shown in blue)
- Join Table4 with B = Result
Other orders of execution are just as valid depending on the business context to which they are applied. Another order of execution will generate a different result. To allow business model designers to ensure that user selections are executed in a specific way, a Join Order Key is added to the Relationship Properties dialog box.
The join order key is relevant only in instances in which outer joins are deployed in business models. To make the importance of the execution order apparent, this information is displayed in the graphical view of the model, as shown below.