Multiple-table join

A multiple-table join connects more than two tables on one or more associated columns; it can be an equi-join or a natural join.

The following query creates an equi-join on the catalog, stock, and manufact tables.

Figure 1. Query

SELECT * FROM catalog, stock, manufact
   WHERE catalog.stock_num = stock.stock_num
      AND stock.manu_code = manufact.manu_code
      AND catalog_num = 10025;

The query retrieves the following rows.

Figure 2. Query result

catalog_num  10025
stock_num    106
manu_code    PRC
cat_descr    
Hard anodized alloy with pearl finish; 6mm hex bolt hard ware. 
Available in lengths of 90-140mm in 10mm increments.
cat_picture  <BYTE value>

cat_advert   ProCycle Stem with Pearl Finish
stock_num    106
manu_code    PRC
description  bicycle stem
unit_price   $23.00
unit         each
unit_descr   each
manu_code    PRC
manu_name    ProCycle
lead_time       9

The manu_code is repeated three times, once for each table, and stock_num is repeated twice.

To avoid the considerable duplication of a multiple-table query such as Figure 1, include specific columns in the projection list to define the SELECT statement more closely, as the following query shows.

Figure 3. Query

SELECT catalog.*, description, unit_price, unit, 
      unit_descr, manu_name, lead_time
   FROM catalog, stock, manufact
   WHERE catalog.stock_num = stock.stock_num
       AND stock.manu_code = manufact.manu_code
       AND catalog_num = 10025;

The query uses a wildcard to select all columns from the table with the most columns and then specifies columns from the other two tables. The result shows the natural join that the query produces. It displays the same information as the previous example, but without duplication.

Figure 4. Query result

catalog_num  10025
stock_num    106
manu_code    PRC
cat_descr    
Hard anodized alloy with pearl finish. 6mm hex bolt 
hardware. Available in lengths of 90-140mm in 10mm increments.
cat_picture  <BYTE value>
                                                  
cat_advert   ProCycle Stem with Pearl Finish
description  bicycle stem
unit_price   $23.00
unit         each
unit_descr   each
manu_name    ProCycle
lead_time       9