Join order examples

The optimizer could select among several plans for this three-way join query:

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

Here are a few examples:

Use c22 as a search argument on t2, join with t1 on c11, then with t3 on c31:

(g_join
    (i_scan i_c22 t2)
    (i_scan i_c11 t1)
    (i_scan i_c31 t3)
)

Use the search argument on t3, and the join order t3, t1, t2:

(g_join
    (i_scan i_c32 t3)
    (i_scan i_c12 t1)
    (i_scan i_c21 t2)
)

Do a full table scan of t2, if it is small and fits in cache, still using the join order t3, t1, t2:
```
(g_join
    (i_scan i_c32 t3)
    (i_scan i_c12 t1)
    (t_scan t2)
)
```
If t1 is very large, and t2 and t3 individually qualify a large part of t1, but together a very small part, this plan specifies a STAR join:
```
(g_join
    (i_scan i_c22 t2)
    (i_scan i_c32 t3)
    (i_scan i_c11_c12 t1)
)
```

All of these plans completely constrain the choice of join order, letting the optimizer choose the type of join.

The generic g_join operator implements outer joins, inner joins, and existence joins. For examples of flattened subqueries that perform existence joins, see “Flattened subqueries”.