ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Key joins

Rules describing the operation of key joins

The following rules summarize the information provided above.

Rule 1: key join of two tables 

This rule applies to A KEY JOIN B, where A and B are base or temporary tables.

1. 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.

2. 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.

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

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

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

   • If there is more than one foreign key between A and B, then the join is ambiguous. The syntax error SQLE_AMBIGUOUS_JOIN (-147) is issued.

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

   • If there is no foreign key, then the join is invalid and an error is generated.

Rule 2: key join of table expressions that do not contain commas 

This rule applies to A KEY JOIN B, where A and B are table expressions that do not contain commas.

1. 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.

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

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

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

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

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

   • If there is no foreign key, then the join is invalid and an error is generated.

Rule 3: key join of table expression lists 

This rule applies to (A1, A2, ...) KEY JOIN ( B1, B2, ...) where A1, B1, and so on are table expressions that do not contain commas.

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 or 2. If any KEY JOIN for a pair of table expressions is ambiguous by Rule 1 or 2, a syntax error is generated.

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

Rule 4: key join of lists and table expressions that do not contain commas 

This rule applies to (A1, A2, ...) KEY JOIN ( B1, B2, ...) where A1, B1, and so on are table expressions that may contain commas.

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.

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