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:
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.
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:
SalesOrders.SalesRepresentative = Employees.EmployeeID.
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;
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|