ASA SQL User's Guide
Joins: Retrieving Data from Several Tables
Key joins
Key joins of table expressions
When table expression lists are joined via key join with table expressions that do not contain commas, Adaptive Server Anywhere generates a join condition for each table in the table expression list.
For example, the following statement is the key join of a table expression list with a table expression that does not contain commas. This example generates a join condition for table A with table expression C NATURAL JOIN D, and for table B with table expression C NATURAL JOIN D.
SELECT * FROM (A,B) KEY JOIN (C NATURAL JOIN D)
(A,B) is a list of table expressions and C NATURAL JOIN D is a table expression. Adaptive Server Anywhere must therefore generate two join conditions: it generates one join condition for the pairs A-C and A-D, and a second join condition for the pairs B-C and B-D. It does so according to the rules for key joins when there are multiple foreign key relationships:
For each set of table-pairs, Adaptive Server Anywhere looks for a foreign key that has the same role name as the correlation name of one of the primary key tables. If there is exactly one foreign key meeting this criterion, it uses it. If there is more than one, the join is ambiguous and an error is issued.
For each set of table-pairs, 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 exactly one relationship, it uses it. If there is more than one, the join is ambiguous and an error is issued.
For each set of pairs, if there is no foreign key relationship, an error is issued.
If Adaptive Server Anywhere is able to determine exactly one join condition for each set of pairs, it combines the join conditions with the keyword AND.
Consider the following join of five tables:
((A,B) JOIN (C NATURAL JOIN D) ON A.x = D.y) KEY JOIN E
In this case, Adaptive Server Anywhere generates a join condition for the key join to E by generating a condition either between (A,B) and E or between C NATURAL JOIN D and E. This is as described in Key joins of table expressions that do not contain commas.
If Adaptive Server Anywhere generates a join condition between (A,B) and E, it needs to create two join conditions, one for A-E and one for B-E. It must find a valid foreign key relationship within each table-pair. This is as described in Key joins of table expression lists.
If Adaptive Server Anywhere creates a join condition between C NATURAL JOIN D and E, it creates only one join condition, and so must find only one foreign key relationship in the pairs C-E and D-E. This is as described in Key joins of table expressions that do not contain commas.
The following is an example of a key join of a table expression and a list of table expressions. The example provides the name and department of employees who are sales representatives and also managers.
SELECT DISTINCT employee.emp_lname, ky_dept_id.dept_name
FROM (sales_order, department AS ky_dept_id)
KEY JOIN (employee JOIN department AS d
ON employee.emp_id = d.dept_head_id)Adaptive Server Anywhere generates two join conditions:
There must be exactly one foreign key relationship between the table-pairs sales_order-employee and sales_order-d. There is; it is sales_order.sales_rep = employee.emp_id.
There must be exactly one foreign key relationship between the table-pairs ky_dept_id-employee and ky_dept_id-d. There is; it is ky_dept_id.dept_id = employee.dept_id.
This example is equivalent to the following. In the following version, it is not necessary to create the correlation name department AS ky_dept_id, because that was only needed to clarify which of two foreign keys should be used to join employee and department.
SELECT DISTINCT employee.emp_lname, department.dept_name
FROM (sales_order, department)
JOIN (employee JOIN department AS d
ON employee.emp_id = d.dept_head_id)
ON sales_order.sales_rep = employee.emp_id
AND department.dept_id = employee.dept_id