ALLBASE/SQL Reference Manual (36216-90216)
132 Chapter3
SQL Queries
Complex Queries
But, the action of the query is subtle. The natural left join preserves the parts from the
Parts table that is not supplied by any vendor. This supplies the left hand component for
the simulated symmetric outer join. However, although the natural right join preserves
the three vendors from the vendors table who do not supply parts (the right hand
component for the simulated symmetric outer join), it eliminates the unmatched parts
from the Parts table. This happens because the natural right join only preserves
unmatched rows from the right hand table, eliminating the row from the Parts table.
NOTE
If you test the next query on the sample database, you must first use the
following ISQL INSERT statement to add a row with no vendor to the Parts
table.
INSERT INTO PurchDB.Parts
(PartNumber, PartName)
VALUES ('XXXX-D-LO', 'test part');
To preserve all the unmatched rows from both sides, thus generating a full symmetric
outer join, you must use the following syntax:
SELECT PartName, PartNumber, VendorName
FROM PurchDB.Parts
NATURAL LEFT JOIN PurchDB.SupplyPrice
NATURAL LEFT JOIN PurchDB.Vendors
UNION
SELECT PartName, PartNumber, VendorName
FROM PurchDB.Parts
NATURAL RIGHT JOIN PurchDB.SupplyPrice
NATURAL RIGHT JOIN PurchDB.Vendors
UNION
SELECT PartName, PartNumber, VendorName
FROM PurchDB.Parts
NATURAL RIGHT JOIN PurchDB.SupplyPrice
NATURAL LEFT JOIN PurchDB.Vendors
ORDER BY PartName DESC, PartNumber;
The result from the natural left join...natural left join preserves the unmatched part from
Parts. The natural right join...natural right join preserves the unmatched vendors from
Vendors. The natural right join...natural left join would preserve all unmatched rows from
SupplyPrice if there were any (in this example there are none). The union operation
combines the three results, preserving the unmatched rows from all joins. There are three
complete sets of rows that satisfy the inner join, but the union operation eliminates the
duplicate rows unless UNION ALL is specified.