Java Reference
In-Depth Information
In many cases, a subquery used in a comparison returns more than one value, so
special predicates are required to operate on the results of the subquery before
making the comparison.
For example, if you want to find out which inventory items cost more than cookies,
you can use a subquery like this:
(SELECT cost FROM inventory
WHERE Description = 'Cookies');
The result of this subquery is several rows of cookie costs, so you need to select
which cost you want to use. The ANY or SOME predicates, which are synonymous,
can be used to retrieve records in the main query that satisfy the comparison with any
records retrieved in this subquery:
SELECT * FROM INVENTORY
WHERE cost >= ANY
(SELECT cost FROM inventory
WHERE Description = 'Cookies');
This query returns all inventory items with a cost greater than or equal to the
lowest-cost cookies in the Inventory Table, as shown here:
Item_Number
Name
Description
Qty
Cost
1001
Corn Flakes
Cereal
178
1.95
1002
Rice Krispies
Cereal
97
2
1003
Shredded Wheat
Cereal
103
2.05
1005
Chocolate Chip
Cookies
217
1.26
1006
Fig Bar
Cookies
162
1.57
1007
Sugar Cookies
Cookies
276
1.03
The ALL predicate can be used to retrieve only records in the main query that satisfy
the comparison with all records retrieved in the subquery. If you change ANY to ALL
in the preceding example, the query returns only those inventory items that cost more
than all cookies:
Item_Number
Name
Description
Qty
Cost
1001
Corn Flakes
Cereal
178
1.95
1002
Rice Krispies
Cereal
97
2
