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. 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 SQL 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:

The join does not have an ON clause, and so is by default a key join.

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;

SELECT *
FROM A LEFT OUTER JOIN B ON A.x = C.x, C;

 See also