in

Description

Identifies the location of a table that is specified in a subquery or view.

Syntax

( in ( [ subq subquery_id | view view_name ] )
)

Parameters

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

view view_name: is the name of a view. The specification of database and owner name in the abstract plan must match the usage in the query in order for plan association to be performed.

Examples

Example 1

create view v1 as 
select * from t1

select * from v1

( t_scan ( table t1 ( in ( view v1 ) ) ) )

Identifies the view in which table t1 is used.

Example 2

select * 
from t2 
where c21 
in (select c12 from t1)

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

Identifies the scan of table t1 in subquery 1.

Example 3

create view v9    
as 
select *
from t1
where c11 in (select c21 from t2)

create view v10
as
select * from v9
where c11 in (select c11 from v9)

select * from v10, t3
where c11 in 
        (select c11 from v10 where c12 = t3.c31)

( g_join 
( t_scan t3 ) 
( i_scan i_c21 ( table t2 ( in ( subq 1 ) ( view v9 ) ( view v10 )))) 
( i_scan i_c11 ( table t1 ( in ( view v9 ) ( view v10 )))) 
( i_scan i_c11 ( table t1 ( in ( view v9 ) ( view v10 ) ( subq 1 )))) 
( i_scan i_c11 ( table t1 ( in ( view v9 ) ( subq 1 ) ( view v10 )))) 
( i_scan i_c21 ( table t2 ( in ( subq 1 ) ( view v9 ) ( subq 1 ) ( view v10 )))) 
( i_scan i_c11 ( table t1 ( in ( view v9 ) ( subq 1 ) ( view v10 ) ( subq 1 )))) 
( i_scan i_c21 ( table t2 ( in ( subq 1 ) ( view v9 ) ( view v10 ) ( subq 1 )))) 
( i_scan i_c21 ( table t2(in( subq 1 )( view v9 )( subq 1 )( view v10 ) ( subq 1)))) 
)

An example of multiple nesting of views and subqueries.

Usage

Identifies the occurrence of a table in view or subqueryof the SQL query.
The in list has the innermost items to the left, near the table’s name (itself the deeply nested item), and the outermost items (the ones occurring in the top level query) to the right. For example, the qualification:
```
(table t2 (in (subq 1) (view v9) (subq 1) (view v10) (subq 1) ) )
```
can be read in either direction:
- Reading left to right, starting from the table: the base table t2 as scanned in the first subquery of view v9 , which occurs in the first subquery of view v10 , which occurs in the first subquery of the main query
- Reading from right to left, that is, starting from the main query: in the main query there’s a first subquery, that scans the view v10 , that contains a first subquery that scans the view v9 , that contains a first subquery that scans the base table t2

in