ALLBASE/SQL Reference Manual (36216-90216)

128 Chapter3
SQL Queries
Complex Queries
.
.
.
---------------------------------------------------------------------
Number of rows selected is 16
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e
When you use the ON clause of the JOIN syntax, it must contain, at a minimum, the
predicate which specifies the join condition. Other predicates may be placed within the
SELECT statement, but their location is critical as the following examples show.
Additional predicates may be placed in the ON clause. These predicates limit the rows
participating in the inner join associated with the ON clause. All rows excluded by such
predicates participate in the outer part of the associated join. The following query returns
(in the inner part of the join) Part Numbers for all vendors who supply parts and are
located in California (
italics ). It also returns, without the Part Number (in the outer part
of the join) all vendors who do not supply parts (BOLD), and all vendors who do supply
parts, but are not located in California.
SELECT PartNumber, VendorName, VendorCity
FROM Purchdb.SupplyPrice sp
RIGHT JOIN PurchdB.Vendors v
ON sp.VendorNumber = v.VendorNumber
AND VendorState = 'CA'
ORDER BY PartNumber DESC
SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT...
---------------+------------------------------+--------------------
PARTNUMBER |VENDORNAME |VENDORCITY
---------------+------------------------------+--------------------
|Underwood Inc. |Atlantic City
|Remington Disk Drives |Concord
|Coupled Systems |Puget Sound
|Kinki Cable Co. |Bakersfield
|Jujitsu Microelectronics |Bethesda
|Dove Computers |Littleton
|SemiTech Systems |San Jose
|KellyCo Inc. |Crabtree
|Educated Boards Inc. |Phoenix
|Chocolate Chips |Lac du Choc
|Morgan Electronics |Braintree
|Eve Computers |Snake River
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 rows participating in the inner join are further restricted by
adding to the ON clause, AND VendorState = 'CA'. All vendors that are not in California are
placed in the outer part of the join.
If you move the limiting predicate from the ON clause to the WHERE clause, the query
returns a different result. In the following query, the inner part of the join still contains all
vendors who supply parts and are located in California. However, in the outer part of the
join, only those vendors who do not supply parts and are in California are included.