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 when there are multiple foreign key relationships Next Page

Key joins of table expressions


SQL Anywhere generates join conditions for the key join of table expressions by examining the foreign key relationship of each pair of tables in the statement.

The following example joins four pairs of tables.

SELECT *
FROM (A NATURAL JOIN B) KEY JOIN (C NATURAL JOIN D);

The table-pairs are A-C, A-D, B-C and B-D. SQL Anywhere considers the relationship within each pair and then creates a generated join condition for the table expression as a whole. How SQL Anywhere does this depends on whether the table expressions use commas or not. Therefore, the generated join conditions in the following two examples are different. A JOIN B is a table expression that does not contain commas, and (A,B) is a table expression list.

SELECT *
FROM (A JOIN B) KEY JOIN C;

is semantically different from

SELECT *
FROM (A,B) KEY JOIN C;

The two types of join behavior are explained in the following sections:


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