Subquery identification and attachment

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