Database Reference
In-Depth Information
Figure 12.3
A Single-Row
Subquery
Returning More
Than One Row
Returns an Error.
12.4.2
Multiple-Row Subqueries
A multiple-row subquery returns multiple rows. The IN, EXISTS, and
Group (ANY, ALL, SOME) comparison conditions allow multiple-row
subquery results. See the syntax diagram in Figure 12.1.
A multiple-row subquery can provide the set of values needed for the
IN comparison condition.
The EXISTS comparison condition usually uses indexes to match
values in the subquery to values in the calling query. Regardless of
correlated indexed columns between calling and subquery, EXISTS
will stop execution of the subquery when the appropriate value is
found. IN will build all values in the set for the subquery before pass-
ing its result back to the calling query. Using EXISTS rather than IN
often results in better performance of the query. EXISTS may not
perform better than IN when the set produced by the subquery is a
limited set of literal values or a very small number of rows.
ANY, ALL, and SOME imply any value, all values, and some values,
respectively. Because these subquery comparison conditions test
against a set of values, a multiple-row query can in reality return zero,
one, or many rows.
Note:
It is important to note that a multiple-row subquery can return zero
rows because the Membership, Exists, and Group comparison conditions
return a set of values. That set of values can be an empty set. An empty set is a
valid set.
Search WWH ::




Custom Search