Natural join

A natural join is a type of equi-join and is structured so that the join column does not display data redundantly, as the following query shows.

Figure 1. Query

SELECT manu_name, lead_time, stock.*
   FROM manufact, stock
   WHERE manufact.manu_code = stock.manu_code;

Like the example for equi-join, the query joins the manufact and stock tables on the manu_code column. Because the Projection list is more closely defined, the manu_code is listed only once for each row retrieved, as the result shows.

Figure 2. Query result

manu_name    Smith
lead_time       3
stock_num    1
manu_code    SMT
description  baseball gloves
unit_price   $450.00
unit         case
unit_descr   10 gloves/case

manu_name    Smith
lead_time       3
stock_num    5
manu_code    SMT
description  tennis racquet
unit_price   $25.00
unit         each
unit_descr   each

manu_name    Smith
lead_time       3
stock_num    6
manu_code    SMT
description  tennis ball
unit_price   $36.00
unit         case
unit_descr   24 cans/case

manu_name    Anza
lead_time       5
stock_num    5
manu_code    ANZ
description  tennis racquet
unit_price   $19.80
unit         each
unit_descr   each

All joins are associative; that is, the order of the joining terms in the WHERE clause does not affect the meaning of the join.

Both statements in the following query create the same natural join.

Figure 3. Query

SELECT catalog.*, description, unit_price, unit, unit_descr
   FROM catalog, stock
   WHERE catalog.stock_num = stock.stock_num
      AND catalog.manu_code = stock.manu_code
      AND catalog_num = 10017;

SELECT catalog.*, description, unit_price, unit, unit_descr
   FROM catalog, stock
   WHERE catalog_num = 10017
      AND catalog.manu_code = stock.manu_code
      AND catalog.stock_num = stock.stock_num;

Each statement retrieves the following row.

Figure 4. Query result

catalog_num  10017
stock_num    101
manu_code    PRC
cat_descr    
Reinforced, hand-finished tubular. Polyurethane belted. 
Effective against punctures. Mixed tread for super wear 
and road grip.
cat_picture  <BYTE value>

cat_advert   Ultimate in Puncture Protection, Tires 
             Designed for In-City Riding
description  bicycle tires
unit_price   $88.00
unit         box
unit_descr   4/box

Query includes a TEXT column, cat_descr; a BYTE column, cat_picture; and a VARCHAR column, cat_advert.