ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 131
SQL Queries
Complex Queries
SELECT DISTINCT v.VendorName
FROM PurchDB.Vendors v, PurchDB.SupplyPrice sp
WHERE v.VendorNumber = sp.VendorNumber
AND sp.UnitPrice < 100.00
The next example shows this query joined to the previous one by the UNION ALL statement.
It also shows the use of character constants to indicate which rows result from which
query block.
SELECT DISTINCT v.VendorName, 'supplies parts under $100'
FROM PurchDB.Vendors v, PurchDB.SupplyPrice sp
WHERE v.VendorNumber = sp.VendorNumber
AND sp.UnitPrice < 100.00
UNION ALL
SELECT v.VendorName, 'none supplied'
FROM PurchDB.Vendors v
WHERE NOT EXISTS (SELECT *
FROM PurchDB.SupplyPrice sp
WHERE sp.VendorNumber = v.VendorNumber)
------------------------------+-------------------------+
VENDORNAME |(CONST) |
------------------------------+-------------------------+
Dove Computers |supplies parts under $100|
Educated Boards Inc. |supplies parts under $100|
Jujitsu Microelectronics |supplies parts under $100|
Proulx Systems Inc. |supplies parts under $100|
Seaside Microelectronics |supplies parts under $100|
Seminational Co. |supplies parts under $100|
Underwood Inc. |supplies parts under $100|
Covered Cable Co. |none supplied |
SemiTech Systems |none supplied |
Chocolate Chips |none supplied |
Symmetric Outer Join Using the UNION Operator
Since the syntax does not support a symmetric outer join, you might try to simulate a
symmetric outer join using the left outer join syntax in combination with the right outer
join syntax. Intuitively, the following query might seem correct:
SELECT PartName, PartNumber, VendorName, VendorCity
FROM Purchdb.Parts
NATURAL LEFT JOIN Purchdb.SupplyPrice
NATURAL RIGHT JOIN Purchdb.Vendors
ORDER BY PartName, VendorName
This three table outer join does a left outer join between the Parts and the SupplyPrice
tables. The result of that join is then used as the left hand table in a right outer join with
the Vendors table.
It would seem as though the result first displays all parts supplied by a vendor, then all
parts for which there is no supplier, followed by all vendors who do not supply parts.