ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 113
SQL Queries
Simple Queries
ALLBASE/SQL creates a row for the query result whenever a part number in table
PurchDB.Parts matches a part number in table PurchDB.SupplyPrice, for example:
PurchDB.Parts:
PARTNUMBER PARTNAME SALESPRICE
--------------------------------------------
1123-P-01 Central processor 500.00
.
.
.
PurchDB.SupplyPrice:
PARTNUMBER VENDORNUMBER ... DISCOUNTQTY
----------------------------------------------
1123-P-01 9002 1
1123-P-01 9003 5
1123-P-01 9007 3
1123-P-01 9008 5
.
.
.
Any row containing a null part number is excluded from the join, as are rows that have a
part number value in one table, but not the other.
You can also join a table to itself. This type of join is useful when you want to compare data
in a table with other data in the same table. In the following example, table
PurchDB.Parts is joined to itself to determine which parts have the same sales price as
part 1133-P-01:
SELECT q.PartNumber, q.SalesPrice
FROM PurchDB.Parts p,
PurchDB.Parts q
WHERE p.SalesPrice = q.SalesPrice
AND p.PartNumber = '1133-P-01'
The same query result is obtained from the following explicit join syntax:
SELECT q.PartNumber, q.SalesPrice
FROM Purchdb.Parts p
JOIN Purchdb.Parts q
ON p.SalesPrice = q.SalesPrice
AND p.PartNumber = '1133-P-01'
To obtain the query result, ALLBASE/SQL joins one copy of the table with another copy of
the table, as follows, using the join condition specified in the WHERE clause or the ON
SearchCondition3 clause:
• You name each copy of the table in the FROM clause by using a correlation name.In
this example, the correlation names are
p and q . You use the correlation names to
qualify column names in the select list and other clauses in the query.
• The join condition in this example specifies that for each sales price, the query result
should contain a row only when the sales price matches that of part 1133-P-01.