|
|
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.
|