ALLBASE/SQL Reference Manual (36216-90216)

124 Chapter3
SQL Queries
Complex Queries
Using the IN Predicate with a Value List
If you wanted to obtain the numbers of all vendors who supplied a given list of parts, the
following query could be used:
SELECT DISTINCT VendorNumber
FROM PurchDB.SupplyPrice
WHERE PartNumber
IN ('1143-P-01', '1323-D-01', '1333-D-01', '1723-AD-01',
'1733-AD-01')
------------
VENDORNUMBER
------------
9004
9007
9008
9009
.
.
.
Using the IN Predicate with a Subquery
If you wanted a list of all the vendors who supply the same parts that vendor 9004
supplies, the following query could be used:
SELECT DISTINCT VendorNumber
FROM PurchDB.SupplyPrice
WHERE PartNumber IN (SELECT PartNumber
FROM PurchDB.SupplyPrice
WHERE VendorNumber = 9004)
------------
VENDORNUMBER
------------
9004
9007
9008
9009
.
.
.
The subquery determines the part number of every part supplied by vendor 9004. The
outer query selects every vendor who supplies one or more of those parts. DISTINCT
removes duplicates from the final query result, as many vendors supply more than one
such part.
EXISTS Predicate
The EXISTS predicate, also known as the existential predicate, tests for the existence of a
row satisfying some condition. It has the following general format:
EXISTS
Subquery
EXISTS is true only if the query result of the subquery is not empty; that is, a row or rows
are returned as a result of the subquery. If the query result is empty, the EXISTS predicate