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”.