Subqueries are identified with numbers, in the order of their leading opened parenthesis “(“.
This example has two subqueries, one in the select list:
select
(select c11 from t1 where c12 = t3.c32), c31
from t3
where c32 > (select c22 from t2 where c21 = t3.c31)
In the abstract plan, the subquery containing t1 is named “1” and the subquery containing t2 is named “2”. Both subquery 1 and 2 are nested over the scan of t3:
( nested
( nested
( t_scan t3 )
( subq 1
( i_scan i_c11_c12 ( table t1 (in ( subq 1 ) ) ) )
)
)
( subq 2
( i_scan i_c21 ( table t2 ( in ( subq 2 ) ) ) )
)
)
In this query, the second subquery is nested in the first:
select * from t3
where c32 > all
(select c11 from t1 where c12 > all
(select c22 from t2 where c21 = t3.c31))
In this case, the subquery containing t1 is also named “1” and the subquery containing t2 is named “2”. In this plan, subquery 2 is nested over the scan of t1, which is performed in subquery 1; subquery 1 is nested over the scan of t3 in the main query:
( nested
( t_scan t3 )
( subq 1
( nested
( i_scan i_c11_c12 ( table t1 ( in ( subq 1 ) ) ) )
( subq 2
( i_scan i_c21 ( table t2 ( in ( subq 2 ) ) ) )
)
)
)
