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.