ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 123
SQL Queries
Complex Queries
avoid large stockpiles of these parts, you want to find vendors whose discount is not
dependent on the purchase of such large quantities. The following query uses two
subqueries and an ALL quantifier to retrieve the information you want:
SELECT VendorNumber, PartNumber, DiscountQty
FROM PurchDB.SupplyPrice
WHERE DiscountQty < ALL (SELECT DiscountQty
FROM PurchDB.SupplyPrice
WHERE VendorNumber = 9010)
AND PartNumber IN (SELECT PartNumber
FROM PurchDB.SupplyPrice
WHERE VendorNumber = 9010)
------------+----------------+-----------
VENDORNUMBER|PARTNUMBER |DISCOUNTQTY
------------+----------------+-----------
9006|1423-M-01 | 1
9007|1433-M-01 | 15
The first subquery obtains the number of parts needed to qualify for a discount for each
part supplied by vendor 9010. Using the quantifier ALL, rows are selected only when the
quantity needed for a discount is less than that needed for any part supplied by 9010. The
second subquery limits the selection to only those part numbers supplied by vendor 9010.
Thus, the query result shows every part supplied by vendor 9010 which can be obtained
from another vendor in smaller quantities with a discount.
IN Predicate
An IN predicate compares a value with a list of values or a number of values derived by the
use of a subquery. The IN predicate has the following general form:
Expression [NOT] IN { ValueList SubQuery }
The ValueList and SubQuery forms of the IN predicate are described separately in the
following sections.
Note that IN is the same as = ANY.