Find all foreign keys from A referencing B.

If there exists a foreign key whose role name is the correlation name of table B, then mark it as a preferred foreign key.

Find all foreign keys from B referencing A.

If there exists a foreign key whose role name is the correlation name of table A, then mark it as a preferred foreign key.

If there is more than one preferred key, the join is ambiguous. The syntax error SQLE_AMBIGUOUS_JOIN (-147) is issued.

If there is a single preferred key, then this foreign key is chosen to define the generated join condition for this KEY JOIN expression.

If there is no preferred key, then other foreign keys between A and B are used:

For each pair of tables; one from expression A and one from expression B, list all foreign keys, and mark all preferred foreign keys between the tables. The rule for determining a preferred foreign key is given in Rule 1, above.

If there is more than one preferred key, then the join is ambiguous. The syntax error SQLE_AMBIGUOUS_JOIN (-147) is issued.

If there is a single preferred key, then this foreign key is chosen to define the generated join condition for this KEY JOIN expression.

If there is no preferred key, then other foreign keys between pairs of tables are used:

For each pair of table expressions Ai and Bj, find a unique generated join condition for the table expression (Ai KEY JOIN Bj) by applying Rule 1 or 2. If any KEY JOIN for a pair of table expressions is ambiguous by Rule 1 or 2, a syntax error is generated.

The generated join condition for this KEY JOIN expression is the conjunction of the join conditions found in step 1.

For each pair of table expressions Ai and Bj, find a unique generated join condition for the table expression (Ai KEY JOIN Bj) by applying Rule 1, 2, or 3. If any KEY JOIN for a pair of table expressions is ambiguous by Rule 1, 2, or 3, then a syntax error is generated.

The generated join condition for this KEY JOIN expression is the conjunction of the join conditions found in step 1.