Filter
Top Products
[Figure: Example of using a subquery in the IN predicate]
Comparing a set of values
Use a quantified predicate as a search condition to specify comparison with a set of quantified values. Use a
comparison operator and quantifier (ALL, SOME, or ANY) in specifying the quantified predicate.
The format of the subquery specification in parentheses is the same as that for a query specification. However, a table
derived from a subquery can only have one column. This means that only one value expression (column name,
function, and so on) can be specified on the left-hand side of the search condition.
A column that cannot be included in a table specified by a subquery (but can be included in a table specified by an
outer subquery) can be specified in the search condition of the subquery. This subquery is called a subquery that has
an outer reference. The following is an example of a subquery that has an outer reference specified in the quantified
predicate:
Example 3:
In this example, values of ITMNO for which ORDERQTY for all CUSTOMER exceeds STOCKQTY are
fetched. The column that cannot be included in the subquery table (ORDER table), that is, the column
(ITMNO) that can be included in the outer query table (ORDER table), is specified in the search
condition. Figure: Example of specifying a quantified predicate shows the retrieval results.
97