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

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Querying and modifying data » 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, 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
 Example 2