ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 127
SQL Queries
Complex Queries
Outer Joins Using Explicit JOIN syntax
Outer joins may be constructed using the explicit JOIN syntax of the SELECT statement
(see the “SELECT” section of the “SQL Statements” chapter). In a two table outer join, the
first table listed in the FROM clause of the SELECT statement is considered the left hand
table and the second is considered the right hand table.
The set of rows in the result may be viewed as the union of the set of rows returned by an
inner join (the inner part of the join) and the set of rows from one table for which no match
is found in the corresponding table (the outer part of the join).
If the unmatched rows from both tables being joined are preserved, the join is a
symmetric outer join. If the rows are preserved from only the left hand table, the join is
a left asymmetric outer join. (The word asymmetric is usually omitted.) If the rows are
preserved from only the right hand table, the join is a right outer join. The current
syntax will allow you to specify either a left outer join or a right outer join, but not a
symmetric outer join. A technique for creating a symmetric outer join using the UNION
operator is described later in the section, “Symmetric Outer Joins Using the UNION
Operator.”
A left outer join obtains the rows from both tables for which there is a matching value in
the common column or columns (the inner part) and the rows from the left hand table for
which there is no match in the right hand table (the outer part). Each unmatched row from
the left hand table is extended with the columns coming from the right hand table. Each
column in that extension has a null value.
A right outer join obtains the rows from both tables for which there is a matching value in
the common column or columns, and the rows from the right hand table for which there is
no match in the left hand table. The unmatched rows from the right hand table are
extended with the columns coming from the left hand table, with null column values
returned in that extension for every result row which has no match in the left hand table.
For example, the following right outer join is between the SupplyPrice and the Vendors
tables. For all vendors who supply parts, it returns the Part Number, Vendor Name and
Vendor City. For all vendors who do not supply parts, it returns just the Vendor Name and
Vendor City.
SELECT PartNumber, VendorName, VendorCity
FROM Purchdb.SupplyPrice sp
RIGHT JOIN PurchdB.Vendors v
ON sp.VendorNumber = v.VendorNumber
ORDER BY PartNumber DESC
SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT...
----------------+----------------------------+--------------------------
PARTNUMBER |VENDORNAME |VENDORCITY
----------------+----------------------------+--------------------------
|Chocolate Chips |Lac du Choc <--Unmatched
|SemiTech Systems |San Jose <--rows from
|Kinki Cable Co. |Bakersfield <--Vendors table
1943-FD-01 |Eve Computers |Snake River
1933-FD-01 |Remington DiskDrives |Concord
1933-FD-01 |Educated Boards Inc. |Phoenix
1933-FD-01 |Latin Technology |San Jose
1933-FD-01 |Space Management Systems |Santa Clara
1933-FD-01 |Eve Computers |Snake River
1923-PA-01 |Jujitsu Microelectronics |Bethesda