subq

Description

Identifies a subquery.

Syntax

( subq subquery_id
             )

Parameters

subquery_id: is an integer identifying the subquery. In abstract plans, subquery numbering is based on the order of the leading parenthesis for the subqueries in a query.

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 subq operator has two meanings in an abstract plan expression:
- Under a nested operator, it describes the attachment of a nested subquery to a table
- Under an in operator, it describes the nesting of the base tables and views that the subquery contains
To specify the attachment of a subquery without providing a plan specification, use an empty hint:
```
( nested
    ( t_scan t1)
    ( subq 1
            ()
    )
```
```
)
```
To provide a description of the abstract plan for a subquery, without specifying its attachment, specify an empty hint as the abstract plan derived table in the nested operator:
```
( nested 
    ()
    ( subq 1
        (t_scan ( table t1 ( in ( subq 1 ) ) ) )
    )
)
```
When subqueries are flattened to a join, the only reference to the subquery in the abstract plan is the identification of the table specified in the subquery:
```
select * 
from t2 
where c21 in (select c12 from t1)
```
```
( nl_g_join 
    ( t_scan t1 ) 
    ( t_scan ( table t2 ( in ( subq 1 ) ) ) ) 
```

When a subquery is materialized, the subquery appears in the store operation, identifying the table to be scanned during the materialization step:

select * 
from t1 
where c11 in (select max(c22) from t2 group by c21)

( plan 
    ( store Worktab1 
        ( t_scan ( table t2 ( in ( subq 1 ) ) ) ) 
    ) 
    ( nl_g_join 
        ( t_scan t1 ) 
        ( t_scan ( work_t Worktab1 ) ) 
    ) 
)