When there is a multiple-table expression on at least one side of a natural join, SQL 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:

In this case, the join (A,B) NATURAL JOIN C causes SQL 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 ( Employees KEY JOIN SalesOrders )
  NATURAL JOIN ( SalesOrderItems KEY JOIN Products );

This is equivalent to

SELECT *
FROM ( Employees KEY JOIN SalesOrders )
  JOIN ( SalesOrderItems KEY JOIN Products )
    ON SalesOrders.ID = SalesOrderItems.ID;