Adaptive Server can flatten some quantified predicate subqueries to a join. Quantified predicate subqueries are introduced with in, any, or exists. Each result row in the outer query is returned once, and only once, if the subquery condition evaluates to TRUE.
