# 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 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 ) )
)
)
```