ALLBASE/SQL Reference Manual (36216-90216)

112 Chapter3
SQL Queries
Simple Queries
Simple Queries
A simple query contains a single SELECT statement and typically has a simple comparison
predicate in the WHERE clause. The SELECT statement can be used to retrieve data from
single tables or from multiple tables. To retrieve data from multiple tables, you join the
tables on a common column value. In the following example, ALLBASE/SQL joins rows
from the PurchDB.SupplyPrice and PurchDB.Parts tables that have the same
PartNumber, as specified in the WHERE clause:
SELECT PartName, VendorNumber
FROM PurchDB.SupplyPrice, PurchDB.Parts
WHERE PurchDB.SupplyPrice.PartNumber =
PurchDB.Parts.PartNumber
The query result is as follows:
-------------------------------|------------
PARTNAME |VENDORNUMBER
-------------------------------|------------
Central Processor | 9002
Central Processor | 9003
Central Processor | 9007
Central Processor | 9008
.
.
.
The following statement, using the explicit JOIN syntax, produces the same query result as
the statement above.
SELECT PartName, VendorNumber
FROM PurchDB.SupplyPrice
JOIN PurchDB.Parts
ON PurchDB.SupplyPrice.PartNumber =
PurchDB.Parts.PartNumber
The same query result is also obtained using the following statement:
SELECT PartName, VendorNumber
FROM PurchDB.SupplyPrice
JOIN PurchDB.Parts
USING (PartNumber)
The following NATURAL JOIN syntax would also produce the same result:
SELECT PartName, VendorNumber
FROM PurchDB.SupplyPrice
NATURAL JOIN PurchDB.Parts
In the four examples above, if a SELECT * is used instead of explicitly naming the
displayed columns in the select list, the query result shows some differences. For the first
two examples, the PartNumber column is displayed twice, once for each of the tables from
which it is derived. For the last two examples, where the USING (
ColumnList ) clause or the
NATURAL JOIN are used, the common columns are coalesced into a single column in the
query result.