There are two basic types of subqueries:
Expression subqueries are introduced with an unmodified comparison operator, must return a single value, and can be used almost anywhere an expression is allowed in SQL.
Quantified predicate subqueries operate on lists that are introduced with in or with a comparison operator that is modified by any or all. Quantified predicate subqueries return zero or more values. This type is also used as an existence test (which checks whether a subquery produces any rows), introduced with exists.
Subqueries of either type are either noncorrelated or correlated (repeating).
A noncorrelated subquery can be evaluated as if it were an independent query. Conceptually, the results of the subquery are substituted in the main statement, or outer query. This is not how Adaptive Server actually processes statements with subqueries. Noncorrelated subqueries can alternatively be stated as joins and are processed as joins by Adaptive Server.
A correlated subquery cannot be evaluated as an independent query, but can reference columns in a table listed in the from list of the outer query. Correlated subqueries are discussed in detail at the end of this chapter.
