Key joins of lists and table expressions that do not contain commas

When table expression lists are joined via key join with table expressions that do not contain commas, SQL 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. SQL 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, SQL 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, SQL 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 SQL Anywhere is able to determine exactly one join condition for each set of pairs, it combines the join conditions with the keyword AND.

Example 1

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, SQL 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 SQL 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 SQL 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.

Example 2

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 Employees.Surname, 
        FK_DepartmentID_DepartmentID.DepartmentName
FROM ( SalesOrders, Departments 
        AS FK_DepartmentID_DepartmentID )
    KEY JOIN ( Employees JOIN Departments AS d
        ON Employees.EmployeeID = d.DepartmentHeadID );

SQL Anywhere generates two join conditions:

There is exactly one foreign key relationship between the table-pairs SalesOrders/Employees and SalesOrders/d: SalesOrders.SalesRepresentative = Employees.EmployeeID.
There is exactly one foreign key relationship between the table-pairs FK_DepartmentID_DepartmentID/Employees and FK_DepartmentID_DepartmentID/d: FK_DepartmentID_DepartmentID.DepartmentID = Employees.DepartmentID.

This example is equivalent to the following. In the following version, it is not necessary to create the correlation name Departments AS FK_DepartmentID_DepartmentID, because that was only needed to clarify which of two foreign keys should be used to join Employees and Departments.

SELECT DISTINCT Employees.Surname, 
   Departments.DepartmentName
FROM ( SalesOrders, Departments )
    JOIN ( Employees JOIN Departments AS d
       ON Employees.EmployeeID = d.DepartmentHeadID )
    ON SalesOrders.SalesRepresentative = Employees.EmployeeID
       AND Departments.DepartmentID = Employees.DepartmentID;

Discuss this page in DocCommentXchange.