m_g_join

Description

Specifies a merge join of two derived tables.

Syntax

```( m_g_join (
( derived_table1 )
( derived_table2 )
)
```

Parameters

derived_table1...derived_tableN

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

Returns

A derived table that is the join of the specified derived tables.

Examples

Example 1

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

Example 2

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

Example 3

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

Usage

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