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:

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:

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;