Quantified predicate subqueries, which return a list of zero and more values, are subqueries in a where or having clause that are connected by any, all, in, or exists. The any or all subquery operators modify comparison operators.
There are three types of quantified predicate subqueries:
any/all subqueries. Subqueries introduced with a modified comparison operator, which may include a group by or having clause, take this general form:
[Start of select, insert, update, delete statement; or subquery]
where expression comparison_operator [any | all] (subquery)
[End of select, insert, update, delete statement; or subquery]
in/not in subqueries. Subqueries introduced with in or not in take this general form:
[Start of select, insert, update, delete statement; or subquery]
where expression [not] in (subquery)
[End of select, insert, update, delete statement; or subquery]
exists/not exists subqueries. Subqueries introduced by exists or not exists are existence tests which take this general form:
[Start of select, insert, update, delete statement; or subquery]
where [not] exists (subquery)
[End of select, insert, update, delete statement; or subquery]
Though Adaptive Server allows the keyword distinct in quantified predicate subqueries, it always processes the subquery as if distinct were not included.
