Union joins

Another type of join is the union join. The derived table of a union join is formed as follows:

• The table to the left of the UNION operator is extended on the right by having the columns of the other table added to it. The added columns are set to the NULL value.

• The table to the right of the UNION operator is extended on the left by having the columns of the other table added to it. The added columns are set to the NULL value.

• The derived table represents the set union of the two extended tables.

Example

Link the ITEMS and PURPOSE tables by means of a union join.

SELECT items.item_num, items.item_name, purpose. *
   FROM items UNION JOIN purpose
   
   item_num     item_name    item_num part          nmuber
   
   1            Bicycle 
   2            Bicycle
   10           Frame   
   11           Frame
   120          Front wheel 
   130          Back wheel
   200          Handlebars 
   ...
   501          Nut M5  
                               1     10              1
                               1    120              1
                               1    130              1
                               1    200              1
                             120    210              1
                             ...
                             200    501              10