Specifying join order

Adaptive Server performs joins of three or more tables by joining two of the tables, and joining the “derived table” from that join to the next table in the join order. This derived table is a flow of rows, as from an earlier nested-loop join in the query execution.

This query joins three tables:

select * 
from t1, t2, t3
where c11 = c21 
    and c12 = c31
    and c22 = 0
    and c32 = 100

This example shows the binary nature of the join algorithm, using g_join operators. The plan specifies the join order t2, t1, t3:

        (scan t2)
        (scan t1)
    (scan t3)

The results of the t2-t1 join are then joined to t3. The scan operator in this example leaves the choice of table scan or index scan up to the optimizer.