Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Joins: Retrieving Data from Several Tables » Key joins

Key joins of lists and table expressions that do not contain commas Next Page

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:

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

Assume this generated join condition is B.y = D.z. You 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 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);

This is equivalent to

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