ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Key joins

Key joins of views and derived tables

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

• For each key join, Adaptive Server 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.

• Adaptive Server 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 cannot contain UNION, ORDER BY, DISTINCT, GROUP BY, or an aggregate function. If it contains any of those items, an error is issued.

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.

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

or

SELECT *
FROM C,D

or

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

Adaptive Server 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, Adaptive Server 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. We can now expand the original join.

SELECT *
FROM View1 KEY JOIN B

is equivalent to

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

For more information, 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 department.dept_name, employee.*
FROM employee JOIN department
  ON employee.emp_id = department.dept_head_id

The following query joins the view to a table expression.

SELECT *
FROM V KEY JOIN (sales_order, department ky_dept_id)

This is equivalent to

SELECT *
FROM V JOIN (sales_order, department ky_dept_id)
ON (V.emp_id = sales_order.sales_rep
AND V.dept_id = ky_dept_id.dept_id)