ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 125
SQL Queries
Complex Queries
is false.
In the following example, suppose you need to determine the names of all vendors who
currently supply parts:
SELECT v.VendorName
FROM PurchDB.Vendors v
WHERE EXISTS (SELECT *
FROM PurchDB.SupplyPrice sp
WHERE sp.VendorNumber = v.VendorNumber)
------------------------------
VENDORNAME
------------------------------
Remington Disk Drives
Dove Computers
Space Management Systems
Coupled Systems
Underwood Inc.
Pro-Litho Inc.
Eve Computers
Jujitsu Microelectronics
Latin Technology
KellyCo Inc.
Morgan Electronics
Seminational Co.
Seaside Microelectronics
Educated Boards Inc.
Proulx Systems Inc.
In this example, v and sp are correlation names, which enable ALLBASE/SQL to
distinguish the two VendorNumber columns in the predicate without requiring you to
repeat each table name in full.
You can also use the NOT EXISTS form of the existential predicate. If you wanted to find
those vendors who are not currently supplying you with parts you could use a query of the
form shown here:
SELECT v.VendorName
FROM PurchDB.Vendors v
WHERE NOT EXISTS (SELECT *
FROM PurchDB.SupplyPrice sp
WHERE sp.VendorNumber = v.VendorNumber)
------------------------------
VENDORNAME
------------------------------
Covered Cable Co.
SemiTech Systems
Chocolate Chips