ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 129
SQL Queries
Complex Queries
SELECT PartNumber, VendorName, VendorCity
FROM Purchdb.SupplyPrice sp
RIGHT JOIN PurchdB.Vendors v
ON sp.VendorNumber = v.VendorNumber
WHERE VendorState = 'CA'
ORDER BY PartNumber DESC
SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT...
----------------+------------------------------+--------------------
PARTNUMBER |VENDORNAME |VENDORCITY
----------------+------------------------------+--------------------
|SemiTech Systems |San Jose
|Kinki Cable Co. |Bakersfield
1933-FD-01 |Latin Technology |San Jose
1933-FD-01 |Space Management Systems |Santa Clara
.
.
.
---------------------------------------------------------------------
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
In the above example, the WHERE clause is applied to all the rows returned, regardless of
whether they are in the inner or outer part of the join. Thus no rows are returned unless
the vendor is located in California.
If you want the inner part of the query to contain all vendors who do supply parts and are
located in California while the outer part contains all vendors who do not supply parts,
regardless of location, use the query shown below.
SELECT PartNumber, VendorName, VendorCity
FROM Purchdb.SupplyPrice sp
RIGHT JOIN PurchdB.Vendors v
ON sp.VendorNumber = v.VendorNumber
WHERE VendorState = 'CA'
OR VendorState <> 'CA' AND PartNumber IS NULL
ORDER BY PartNumber DESC
SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp
RIGHT...
----------------+------------------------------+--------------------
PARTNUMBER |VENDORNAME |VENDORCITY
----------------+------------------------------+--------------------
|SemiTech Systems |San Jose
|Chocolate Chips |Lac du Choc
|Kinki Cable Co. |Bakersfield
1933-FD-01 |Latin Technology |San Jose
1933-FD-01 |Space Management Systems |Santa Clara
1923-PA-01 |Seminational Co. |City of Industry
1833-PT-01 |Seminational Co. |City of Industry
1833-PT-01 |Seaside Microelectronics |Oceanside
1823-PT-01 |Seaside Microelectronics |Oceanside
.
.
.
---------------------------------------------------------------------
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
If all common columns between the tables being joined are to be used for the join, the