ALLBASE/SQL Reference Manual (36216-90216)

122 Chapter3
SQL Queries
Complex Queries
Using ANY or SOME with a Subquery
You can also use the subquery form of the quantified predicate. If you wanted to distribute
some of the business you have been giving vendor 9004, you might want to find vendor
numbers for each vendor supplying at least one part supplied by vendor 9004. The
following query returns this information:
SELECT DISTINCT VendorNumber
FROM PurchDB.SupplyPrice
WHERE PartNumber = ANY (SELECT PartNumber
FROM PurchDB.SupplyPrice
WHERE VendorNumber = 9004)
------------
VENDORNUMBER
------------
9004
9007
9008
9009
9011
9012
9015
The subquery obtains the part numbers for all parts supplied by vendor 9004. The
quantifier ANY is then used to determine if PartNumber is the same as any of these parts.
If so, the vendor number supplying that part is returned in the query result.
Some queries may require you to use ANY and SOME constructs in a manner that is not
intuitive. Consider the following query:
SELECT T1.SalesPrice
FROM T1
WHERE T1.PartNumber <> ANY (SELECT T2.PartNumber
FROM T2)
The inexperienced SQL user might think that this means, “Select the sales price of parts
from table T1 whose numbers are
not equal to any part numbers in table T2.” However,
the actual meaning is, “Select the sales price of parts from T1 such that the part number
from T1 is
not equal to at least one part number in T2.” This query returns the sales
price of all the parts in T1 if T2 has more than one part.
A less ambiguous form using EXISTS is as follows:
SELECT T1.SalesPrice
FROM T1
WHERE EXISTS (SELECT T2.PartNumber
FROM T2
WHERE T2.PartNumber <> T1.PartNumber)
Using the ALL Quantifier
With the ALL quantifier, the predicate is true only if
all of the values in the value list or
subquery relate to the expression as indicated by the comparison operator.
Assume you have been buying parts from vendor 9010. To get a discount from this vendor,
you have been required to purchase parts in larger quantities than you would like. To