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

Key joins of table expressions

Adaptive Server 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. Adaptive Server Anywhere considers the relationship within each pair and then creates a generated join condition for the table expression as a whole. How Adaptive Server 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 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