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.

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,
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: