ASA SQL User's Guide
Joins: Retrieving Data from Several Tables
Inner and outer joins
Outer joins
The order of joins is important when a query includes table expressions using outer joins. For example, A JOIN B LEFT OUTER JOIN C is interpreted as (A JOIN B) LEFT OUTER JOIN C. This means that the table expression (A JOIN B) is joined to C. The table expression (A JOIN B) is preserved and table C is null-supplying.
Consider the following statement, in which A, B and C are tables:
SELECT * FROM A LEFT OUTER JOIN B RIGHT OUTER JOIN C
To understand this statement, first remember that Adaptive Server Anywhere evaluates statements from left to right, adding parentheses. This results in
SELECT * FROM (A LEFT OUTER JOIN B) RIGHT OUTER JOIN C
Next, you may want to convert the right outer join to a left outer join so that both joins are the same type. To do this, simply reverse the position of the tables in the right outer join, resulting in:
SELECT * FROM C LEFT OUTER JOIN (A LEFT OUTER JOIN B)
A is the preserved table and B is the null-supplying table for the nested outer join. C is the preserved table for the first outer join.
You can interpret this join as follows:
Join A to B, preserving all rows in A.
Next, join C to the results of the join of A and B, preserving all rows in C.
The join does not have an ON phrase, and so is by default a key join. The way Adaptive Server Anywhere generates join conditions for this type of join is explained in Key joins of table expressions that do not contain commas.
In addition, the join condition for an outer join must only include tables that have previously been referenced in the FROM clause. This restriction is according to the ANSI/ISO standard, and is enforced to avoid ambiguity. For example, the following two statements are syntactically incorrect, because C is referenced in the join condition before the table itself is referenced.
SELECT * FROM (A LEFT OUTER JOIN B ON B.x = C.x) JOIN C
and
SELECT * FROM A LEFT OUTER JOIN B ON A.x = C.x, C