Database

SQL - Multiple table joins

Inner Joins

If we wish to join the two tables to find out what fruit belongs with which preparation we could produce the output shown

Note that this only matches preparations that have fruit. In the database the resultant "joined" table is actually a "Ghost" or virtual table. Microsoft Access refers to this as a dynaset, or more traditionally as a subschema.

• Using the SQL Language this translates to;
  • SELECT * From Fruit, Preparation
    WHERE Fruit_id = Preparation_id;
• This join is known as an "Inner join" or in SQL terms may be written as;
  • SELECT * From Fruit INNER JOIN Preparation
    ON Fruit_id = Preparation_id;

Outer Joins

To list fruit or preparations when a match doesn't occur requires an outer join, so;

• list all of the fruit, and the records that match in the preparation table
  • SELECT * From Fruit LEFT OUTER JOIN Preparation
    ON Fruit_id = Preparation_id;

• list all the preparations and the matching fruit
  • SELECT * From Fruit RIGHT OUTER JOIN Preparation
    ON Fruit_id = Preparation_id;

• list all the fruit and all the preparations;
  • SELECT * From Fruit FULL OUTER JOIN Preparation
    ON Fruit_id = Preparation_id;