The XDB Server uses a number of optimization techniques for processing joins. When performing a multitable join, performance might improve significantly if you specify all possible join conditions; this allows the system to perform the joins with the optimal selectivity ratio first, thus reducing the number of comparisons required.
For example, imagine three tables: EMPLOYEE, DEPARTMENT and PROJECT. Every employee works in a department. Every project is the responsibility of a department. Every project is managed by an employee from the responsible department.
Thus there are three possible join conditions that can be used to join the three tables:
While it is sufficient to provide any two of these join conditions in your query, you might experience significant performance improvement if you provide all three join conditions.
As a general rule, when joining n tables, you should provide at least n-1 join conditions. When the context of a query permits, optimal performance might be achieved by providing any additional, valid join conditions. However the use of redundant join conditions must be balanced against the readability and parsing cost of the query.