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:

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.