ALLBASE/SQL Reference Manual (36216-90216)
130 Chapter3
SQL Queries
Complex Queries
keyword NATURAL may be used so long as the specification of the ON clause join predicate is
omitted. This technique may be used when joining more than two tables, as in the query
shown below:
SELECT PartName, DeliveryDays, VendorName
FROM PurchDB.Parts
NATURAL RIGHT JOIN PurchDB.SupplyPrice
NATURAL RIGHT JOIN PurchDB.Vendors
ORDER BY PartName DESC
SELECT PartName, DeliveryDays, VendorName FROM PurchDB.Parts NATURAL RIGHT...
------------------------------+------------+-------------------------
PARTNAME |DELIVERYDAYS|VENDORNAME
------------------------------+------------+-------------------------
| |SemiTech Systems
| |Kinki Cable Co.
| |Chocolate Chips
Winchester Drive | 20|Remington Disk Drives
Winchester Drive | 30|Morgan Electronics
Video Processor | 20|Latin Technology
Video Processor | 30|Jujitsu Microelectronics
Video Processor | 15|Eve Computers
.
.
.
---------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e
Outer Joins Using the UNION Operator
An outer join can also be created by using the UNION operator.
Suppose you want to create a list of vendors who either supply some part with a unit price
less than $100 or else do not supply any parts at all. To do this, merge two separate queries
with a UNION ALL statement, as in the following examples.
The first query shown here selects the names of vendors who do
not
supply parts:
SELECT v.VendorName
FROM PurchDB.Vendors v
WHERE NOT EXISTS (SELECT *
FROM PurchDB.SupplyPrice sp
WHERE sp.VendorNumber = v.VendorNumber)
Notice that a second query block is embedded within the first query expression. It creates a
temporary table containing the names of all vendors who
do
supply parts. Then note the
special predicate EXISTS, which is negated in this case. The outer SELECT statement
allows us to identify the name of each vendor in the Vendors table. Each VendorName is
compared against the list of vendors who
do
supply parts. If the VendorName from the
outer SELECT statement is
not
found in the temporary table created by the subquery, the
outer VendorName is returned to the query result, providing us a list of all the Vendors
who
do not
supply parts.
The second query shown here defines the vendors who supply at least one part with a unit
price under $100: