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

Key joins of table expressions that do not contain commas

When both of the two table expressions being joined do not contain commas, Adaptive Server Anywhere examines the foreign key relationships in the pairs of tables in the statement, and generates a single join condition.

For example, the following join has two table-pairs, A-C and B-C.

(A NATURAL JOIN B) KEY JOIN C

Adaptive Server Anywhere generates a single join condition for joining C with (A NATURAL JOIN B) by looking at the foreign key relationships within the table-pairs A-C and B-C. It generates one join condition for the two pairs according to the rules for determining key joins when there are multiple foreign key relationships:

• First, it looks at both A-C and B-C for a single foreign key that has the same role name as the correlation name of one of the primary key tables it references. If there is exactly one foreign key meeting this criterion, it uses it. If there is more than one foreign key with the same role name as the correlation name of a table, the join is considered to be ambiguous and an error is issued.

• If there is no foreign key with the same name as the correlation name of a table, Adaptive Server 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.

• If there is no foreign key relationship, an error is issued.

For more information, see Key joins when there are multiple foreign key relationships.

Example 

The following query finds all the employees who are sales representatives, and their departments.

SELECT employee.emp_lname, ky_dept_id.dept_name
FROM (employee KEY JOIN department as ky_dept_id)
  KEY JOIN sales_order

You can interpret this query as follows.

• Adaptive Server Anywhere considers the table expression (employee KEY JOIN department as ky_dept_id) and generates the join condition employee.dept_id    =    ky_dept_id.dept_id based on the foreign key ky_dept_id.

• Adaptive Server Anywhere then considers the table-pairs employee-sales_order and ky_dept_id-sales_order. Note that only one foreign key can exist between the tables sales_order and employee and between sales_order and ky_dept_id, or the join is ambiguous. As it happens, there is exactly one foreign key relationship between the tables sales_order and employee (ky_so_employee_id), and no foreign key relationship between sales_order and ky_dept_id. Hence, the generated join condition is sales_order.emp_id    =    employee.sales_rep.

The following query is therefore equivalent to the previous query:

SELECT employee.emp_lname, department.dept_name
FROM (employee JOIN department
   ON ( employee.dept_id = department.dept_id ) )
JOIN sales_order
   ON (employee.emp_id = sales_order.sales_rep)