The nested operator has the derived table as the first operand and the nested subquery as the second operand. This allows an easy vertical reading of the join order and subquery placement:
select *
from t1, t2, t3
where c12 = 0
and c11 = c21
and c22 = c32
and 0 < (select c21 from t2 where c22 = t1.c11)
In the plan, the join order is t1, t2, t3, with the subquery nested over the scan of t1:
( g_join
( nested
( i_scan i_c11 t1 )
( subq 1
( t_scan ( table t2 ( in (subq 1 ) ) )
)
)
( i_scan i_c21 t2 )
( i_scan i_c32 t3 )
)
