g_join

Description

Specifies the join of two or more derived tables without specifying the join type (nested-loop or sort-merge).

Syntax

( g_join derived_table1 derived_table2
)

( g_join    ( derived_table1 )
                ( derived_table2 )
                 ...
                ( derived_tableN )
)

Parameters

derived_table1...derived_tableN: are the derived tables to be united.

Returns

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

Examples

Example 1

select *
from t1, t2
where c21 = 0
and c22 = c12

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

Table t2 is the outer table, and t1 the inner table in the join order.

Example 2

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

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

Table t2 is joined with t1, and the derived table is joined with t3.

Usage

The g_join operator is a generic logical operator that describes all binary joins (inner join, outer join, or existence join).
The g_join operator is never used in generated plans; nl_g_join and m_g_join operators indicate the join type used.
The optimizer chooses between a nested-loop join and a sort-merge join when the g_join operator is used. To specify a sort-merge join, use m_g_join. To specify a nested-loop join, use nl_g_join.

The syntax provides a shorthand method of described a join involving multiple tables. This syntax:

( g_join
        ( scan t1)
        ( scan t2)
        ( scan t3)
        ...
        ( scan tN-1)
        ( scan tN)
)

is shorthand for:

( g_join
        ( g_join
                ...
                    ( g_join
                            (g_join
                                    ( scan t1)
                                    ( scan t2)
                            )
                            ( scan t3)
                    )
                ...
                ( scan tN-1)
        )
        ( scan tN)
)

If g_join is used to specify the join order for some, but not all, of the tables in a query, the optimizer uses the join order specified, but may insert other tables between the g_join operands. For example, for this query:
```
select *
    from t1, t2, t3
    where ...
```
the following partial abstract plan describes only the join order of t1 and t2:
```
( g_join
        ( scan t2)
        ( scan t1)
)
```
The optimizer can choose any of the three join orders: t3-t2-t1, t2-t3-t1 or t2-t1-t3.
The tables are joined in the order specified in the g_join clause.
If set forceplan on is in effect, and query association is also enabled for the session, forceplan is ignored if a full abstract plan is used to optimize the query. If a partial plan does not completely specify the join order:
- First, the tables in the abstract plan are ordered as specified.
- The remaining tables are ordered as specified in the from clause.
- The two lists of tables are merged.