Java Reference
In-Depth Information
You can use a subquery in a
WHERE
or
HAVING
clause or, in rarer instances, you can use a subquery
instead of an expression in the field list of a
SELECT
statement.
In a subquery you use a
SELECT
statement to provide a set of one or more specific values to evaluate
in the
WHERE
or
HAVING
clause expression, or to provide the returned values of a
SELECT
command
directly, as part of the
SELECT
list.
Subqueries can be used in
WHERE
or
HAVING
clauses as the right-hand side of:
A comparison using
ANY
,
ALL
, or
SOME
An expression using
IN
or
NOT IN
An expression using
EXISTS
or
NOT EXISTS
Using the ANY, SOME, and ALL Predicates
In many cases, a subquery used in a comparison will return more than one value. Because of this, you
need special predicates 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 could use a
subquery like this:
(SELECT cost FROM inventory
WHERE Description = 'Cookies');
The result of this subquery will be several rows of cookie costs, so you will 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 the subquery. The following
example returns all inventory items with a cost greater than the lowest cost cookies in the Inventory
table:
SELECT * FROM INVENTORY
WHERE cost >= ANY
(SELECT cost FROM inventory
WHERE Description = 'Cookies');
The
ALL
predicate can be used to retrieve only those records in the main query that satisfy the
comparison with all records retrieved in the subquery. If you changed
ANY
to
ALL
in the preceding
example, the query would return only those inventory items that cost more than all cookies, as
illustrated in
Figure 7-1
.
