HP 3000 Manuals HP 3000 Manuals
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.
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. ALLBASE/SQL joins a row in
q.PurchDB.Parts to a row in p.PurchDB.Parts having a part number
of 1133-P-01 whenever the SalesPrice value in q.PurchDB.Parts
matches that for 1133-P-01.
The query result for this self-join appears as follows:
----------------------|--------------
PARTNUMBER |SALESPRICE
----------------------|--------------
1133-P-01 | 200.00
1323-D-01 | 200.00
1333-D-01 | 200.00
1523-K-01 | 200.00
For a two or more table join, if you do not use a join predicate in the
ON SearchCondition3 clause or the WHERE clause, or if there are no common
columns with which to join the tables in a natural join, the result of
the join is the Cartesian product. In the simplest case, for a two table
join, the Cartesian product is the set of rows which contains every
possible combination of each row in the first table concatenated with
each row in the second table.
As an example, consider the simple Parts and Colors tables:
Parts Colors
PartNumber PartName PartNumber Color
--------------------- -----------------------
1 Widgit NULL Red
NULL Thing 2 NULL
3 NULL 3 Green
The following query generates the Cartesian product:
SELECT p.PartNumber, PartName, c.PartNumber, Color
FROM Parts p, Colors c
The Cartesian product is shown in the query result:
SELECT p.PartNumber, PartName, c.PartNumber, Color FROM Parts p, Colors c
---------------+------------+----------------+-------------------
PARTNUMBER |PARTNAME |PARTNUMBER |COLOR
---------------+------------+----------------+-------------------
1 |Widgit | NULL|Red
1 |Widgit | 2|NULL
1 |Widgit | 3|Green
NULL |Thing | NULL|Red
NULL |Thing | 2|NULL
NULL |Thing | 3|Green
3 |NULL | NULL|Red
3 |NULL | 2|NULL
3 |NULL | 3|Green
The same algorithm is used to form the Cartesian product for a three or
more table join. Thus, it can be said that the Cartesian product of a
set of n tables is the table consisting of all possible rows r, such that
r is the concatenation of a row from the first table, a row from the
second table,..., and a row from the nth table.
As you can see, the Cartesian product for even a small two table join is
much larger than the source tables. For a three or more table join of
several large tables, the Cartesian product can be so large as to cause
you to run out of memory and generate an error. Therefore it is
important to be sure that you include the appropriate join predicate in
your queries and to be sure that you specify columns common to the tables
being joined.
In the example above, NULLs are included in the tables to show the
difference between the behavior of NULLs in the production of the
Cartesian product and the behavior of NULLs when a common column is
specified in the WHERE clause join predicate.
Consider the following query:
SELECT p.PartNumber, PartName, c.PartNumber, Color
FROM Parts p, Colors c
WHERE p.PartNumber = c.PartNumber
The query result for the query is as follows:
SELECT p.PartNumber, PartName, c.PartNumber, Color FROM Parts p, Colors c....
---------------+------------+----------------+-------------------
PARTNUMBER |PARTNAME |PARTNUMBER |COLOR
---------------+------------+----------------+-------------------
3 |NULL | 3|Green
The only rows selected for the query result are those rows for which the
join predicate (p.PartNumber = c.PartNumber) evaluates to true. Because
NULL has an undetermined value, for the cases where the values of the
predicate are NULL = NULL, the value of the predicate is undetermined,
and the row is not selected.
However, for the Cartesian product shown in the prior example, due to the
absence of a join predicate, rows with NULLs in the common column are
selected because the operation is the simple concatenation of the rows,
regardless of value.