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

Natural joins of table expressions

When there is a multiple-table expression on at least one side of a natural join, Adaptive Server Anywhere generates a join condition by comparing the set of columns for each side of the join operator, and looking for columns that have the same name.

For example, in the statement

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

there are two table expressions. The column names in the table expression A JOIN B are compared to the column names in the table expression C JOIN D, and a join condition is generated for each unambiguous pair of matching column names. An unambiguous pair of matching columns means that the column name occurs in both table expressions, but does not occur twice in the same table expression.

If there is a pair of ambiguous column names, an error is issued. However, a column name may occur twice in the same table expression, as long as it doesn't also match the name of a column in the other table expression.

Natural joins of lists 

When a list of table expressions is on at least one side of a natural join, a separate join condition is generated for each table expression in the list.

Consider the following tables:

• table A consists of columns called a, b and c

• table B consists of columns called a and d

• table C consists of columns called d and c

In this case, the join (A,B) NATURAL JOIN C causes Adaptive Server Anywhere to generate two join conditions:

ON A.c = C.c
 AND B.d = C.d

If there is no common column name for A-C or B-C, an error is issued.

If table C consists of columns a, d, and c, then the join (A,B) NATURAL JOIN C is invalid. The reason is that column a appears in all three tables, and so the join is ambiguous.

Example 

The following example answers the question: for each sale, provide information about what was sold and who sold it.

SELECT *
FROM (employee KEY JOIN sales_order)
  NATURAL JOIN (sales_order_items KEY JOIN product)

This is equivalent to

SELECT *
FROM (employee KEY JOIN sales_order)
  JOIN (sales_order_items KEY JOIN product)
    ON sales_order.id = sales_order_items.id