Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Querying and modifying data » Joins: Retrieving data from several tables » Key joins » Key joins of table expressions

 

Key joins of table expression lists

To generate a join condition for the key join of two table expression lists, SQL Anywhere examines the pairs of tables in the statement, and generates a join condition for each pair. The final join condition is the conjunction of the join conditions for each pair. There must be a foreign key relationship between each pair.

The following example joins two table-pairs, A-C and B-C.

SELECT *
FROM ( A,B ) KEY JOIN C;

SQL Anywhere generates a join condition for joining C with (A,B) by generating a join condition for each of the two pairs A-C and B-C. It does so according to the rules for key joins when there are multiple foreign key relationships:

  • For each pair, SQL Anywhere looks for a foreign key that has the same role name as the correlation name of the primary key table. If there is exactly one foreign key meeting this criterion, it uses it. If there is more than one, the join is considered to be ambiguous and an error is issued.

  • For each pair, if there is no foreign key with the same name as the correlation name of the table, SQL Anywhere looks for any foreign key relationship between the tables. If there is one, it uses it. If there is more than one, the join is considered to be ambiguous and an error is issued.

  • For each pair, if there is no foreign key relationship, an error is issued.

  • If SQL Anywhere is able to determine exactly one join condition for each pair, it combines the join conditions using AND.

See also Key joins when there are multiple foreign key relationships.

 Example