ALLBASE/SQL Reference Manual (36216-90216)

114 Chapter3
SQL Queries
Simple Queries
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
n th 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.