m_g_join

Specifies a merge join of two abstract plan derived tables.

( m_g_join (
      ( derived_table1 )
      ( derived_table2 )
)

derived_table1...derived_tableN: are the abstract plan derived tables to be united. derived_table1 is always the outer table and derived_table2 is the inner table

An abstract plan derived table that is the join of the specified abstract plan derived tables.

select t1.c11, t2.c21
    from t1, t2, t3
    where t1.c11 = t2.c21
        and t1.c11 = t3.c31

( nl_g_join 
        ( m_g_join 
            ( i_scan i_c31 t3 ) 
            ( i_scan i_c11 t1 ) 
        ) 
        ( t_scan t2 ) 
)

Specifies a right-merge join of tables t1 and t3, followed by a nested-loop join with table t2.

select * from t1, t2, t3
where t1.c11 = t2.c21 and t1.c11 = t3.c31
and t2.c22 =7

( nl_g_join 
    ( m_g_join 
        ( i_scan i_c21 t2 ) 
        ( i_scan i_c11 t1 ) 
    ) 
    ( i_scan i_c31 t3 ) 
)

Specifies a full-merge join of tables t2 (outer) and t1 (inner), followed in the join order by a nested-loop join with t3.

select c11, c22, c32
from t1, t2, t3
where t1.c11 = t2.c21
and t2.c22 = t3.c32

( m_g_join
        (nl_g_join
            (i_scan i_c11 t1)
            (i_scan i_c12 t2)
        )
    (i_scan i_c32_ix t3)
)

Specifies a nested-loop join of t1 and t2, followed by a merge join with t3.

The tables are joined in the order specified in the m_g_join clause.
The sort step and worktable required to process sort-merge join queries are not represented in abstract plans.
If the m_g_join operator is used to specify a join that cannot be performed as a merge join, the specification is silently ignored.