nested

Description

Describes the nesting of subqueries on a derived table.

Syntax

( nested 
           ( derived_table )
                 ( subquery_specification )
)

Parameters

derived_table: is the derived table over which to nest the specified subquery.

subquery_specification: is the subquery to nest over derived_table

Returns

A derived table.

Examples

Example 1

select c11 from t1
where c12 = 
        (select c21 from t2 where c22 = t1.c11)

( nested 
        ( t_scan t1 ) 
        ( subq 1 
                ( t_scan ( table t2 ( in ( subq 1 ) ) ) ) 
        ) 
)

A single nested subquery.

Example 2

select c11 from t1
where c12 = 
        (select c21 from t2 where c22 = t1.c11)
    and c12 = 
        (select c31 from t3 where c32 = t1.c11)

( nested 
        ( nested 
                ( t_scan t1 ) 
                ( subq 1 
                        ( t_scan ( table t2 ( in ( subq 1 ) ) ) ) 
                ) 
        ) 
        ( subq 2 
                ( t_scan ( table t3 ( in ( subq 2 ) ) ) ) 
        ) 
)

The two subqueries are both nested in the main query.

Example 3

select c11 from t1
where c12 = 
    (select c21 from t2 where c22 = 
        (select c31 from t3 where c32 = t1.c11))

( nested 
        ( t_scan t1 ) 
        ( subq 1 
                ( nested 
                        ( t_scan ( table t2 ( in ( subq 1 ) ) ) ) 
                        ( subq 2 
                              ( t_scan ( table t3 ( in ( subq 2 ) ) ) ) 
                        ) 
                ) 
        ) 
)

A level 2 subquery nested into a level 1 subquery nested in the main query.

Usage

The subquery is executed at the specified attachment point in the query plan.
Materialized and flattened subqueries do not appear under a nested operator. See subq for examples.