Multi-Valued Subqueries

A multi-value subquery retrieves more than one value and has two forms of syntax, as shown below.

SELECT ... FROM ... WHERE expression rel_oper  {{ANY | SOME} | ALL} (SELECT expression FROM ...)

or

SELECT ... FROM ... WHERE expression [NOT] IN (SELECT expression FROM ...)

The ANY or SOME qualifier (they are synonyms) indicates that the relational operation is true if there is at least one row from the subquery's result set for which it is true. The ALL qualifier indicates that the relational operation is true only when it is true for every row from the subquery's result set.

The IN ( subquery ) relational operation is true if there is one row from the subquery result set that is equal to the value of the left-side expression. If NOT IN is specified the relational operation is true when the value of the left-side expression does not equal any of the subquery result row values.

Note that,

WHERE expression IN (SELECT expression FROM ...)

is the same as

WHERE expression = SOME (SELECT expression FROM ...)

For example, the following query uses a subquery to retrieve a list of books that cost more than the most expensive book purchased after Jan 1, 2010.

select date_acqd, cost, bookid from book
     where cost > all
             (select max(cost) from book
                     where date_acqd > date "2010-01-01");

DATE_ACQD          COST BOOKID
2006-01-02    129629.00 shakespeare01
2008-02-09    185185.00 shakespeare04
2009-07-17    166666.00 shakespeare05