Key joins of views and derived tables

When you include a view or derived table in a key join, SQL Anywhere follows the same basic procedure as with tables, but with these differences:

For each key join, SQL Anywhere considers the pairs of tables in the FROM clause of the query and the view, and generates one join condition for the set of all pairs, regardless of whether the FROM clause in the view contains commas or join keywords.
SQL Anywhere joins the tables based on the foreign key that has the same role name as the correlation name of the view or derived table.
When you include a view or derived table in a key join, the view or derived table definition cannot contain UNION, INTERSECT, EXCEPT, ORDER BY, DISTINCT, GROUP BY, aggregate functions, window functions, TOP, FIRST, START AT, or FOR XML. If it contains any of these items, an error is returned. In addition, the derived table cannot be defined as a recursive table expression.

A derived table works identically to a view. The only difference is that instead of referencing a predefined view, the definition for the table is included in the statement.

For information about recursive table expressions, see Recursive common table expressions, and RecursiveTable algorithm (RT).

Example 1

For example, in the following statement, View1 is a view.

SELECT *
FROM View1 KEY JOIN B;

The definition of View1 can be any of the following and result in the same join condition to B. (The result set will differ, but the join conditions will be identical.)

SELECT *
FROM C CROSS JOIN D;

SELECT *
FROM C,D;

SELECT *
FROM C JOIN D ON (C.x = D.y);

In each case, to generate a join condition for the key join of View1 and B, SQL Anywhere considers the table-pairs C-B and D-B, and generates a single join condition. It generates the join condition based on the rules for multiple foreign key relationships described in Key joins of table expressions, except that it looks for a foreign key with the same name as the correlation name of the view (rather than a table referenced in the view).

Using any of the view definitions above, you can interpret the processing of View1 KEY JOIN B as follows:

SQL Anywhere generates a single join condition by considering the table-pairs C-B and D-B. It generates the join condition according to the rules for determining key joins when there are multiple foreign key relationships:

First, it looks at both C-B and D-B for a single foreign key that has the same role name as the correlation name of the view. 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 the view, 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 the view, SQL 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.

Assume this generated join condition is B.y = D.z. You can now expand the original join. For example, the following two statements are equivalent:

SELECT *
FROM View1 KEY JOIN B;

SELECT *
FROM View1 JOIN B ON B.y = View1.z;

See Key joins when there are multiple foreign key relationships.

Example 2

The following view contains all the employee information about the manager of each department.

CREATE VIEW V AS
SELECT Departments.DepartmentName, Employees.*
FROM Employees JOIN Departments
  ON Employees.EmployeeID = Departments.DepartmentHeadID;

The following query joins the view to a table expression.

SELECT *
FROM V KEY JOIN ( SalesOrders, 
   Departments FK_DepartmentID_DepartmentID );

The following query is equivalent to the previous query:

SELECT *
FROM V JOIN ( SalesOrders, 
   Departments FK_DepartmentID_DepartmentID )
ON ( V.EmployeeID = SalesOrders.SalesRepresentative
AND V.DepartmentID = 
    FK_DepartmentID_DepartmentID.DepartmentID );

Discuss this page in DocCommentXchange.