Types of Joins

Introduction

When studying data relationships, we saw the role of the primary and foreign keys in maintaining the exchange of information between two tables. This technique of linking tables plays a major part when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables as it is applied to a query, the SQL supports two types of joins.

Inner Joins

Imagine you have two tables that can be linked through one's primary key and another's foreign key.

Notice that some records in the Persons table don't have an entry. When creating a query of records of the Persons table, if you want your list to include only records that have an entry, you can create it as inner join. To do this, you would specify the TypeOfJoin factor of our formula with the expression INNER JOIN. Here is an example: SELECT Persons.LastName, Persons.FirstName, Persons.GenderID, Genders.GenderID, Genders.Gender FROM Persons INNER JOIN Genders ON Persons.GenderID = Genders.GenderID This would produce:

By default, when creating a new query, if a relationship was already established between both tables, the query is made an inner join. If there was no relationship explicitly established between both tables, you would have to appropriately edit the SQL statement. In this case, you have two options. You can drag the primary key from the parent table and drop it on the foreign key in the child table:

As another option, you can edit the SQL statement manually to make it an inner join.

We mentioned earlier that you could include all columns in the query. In our result, since we are more interested in the gender of each Persons record, we would not need the GenderID column from the Genders table.

Practical Learning: Using Inner Joins

1. On the Database window, click the Queries button
2. On the main menu, click Insert -> Query
3. In the New Query dialog box, double-click Design View
4. On the Show Table dialog box, double-click Categories and Cars
5. Click Close
6. In the Categories list, double-click Category
7. In the Cars list, double-click Make, Model, CarYear, and Available
8. Save the query as CarsInventory
    
   
9. Right-click somewhere in the window and click SQL View
    
   
10. Notice the INNER JOIN expression in the statement.
    To preview the result, right-click the title bar of the window and click Datasheet View
     
    
11. After viewing the result, right-click the title bar of the window and click Query Design

Outer Joins

Introduction

Instead of showing only records that have entries in the child table, you may want your query to include all records, including those that are null. To get this result, you would create an outer join. You have three options.

Left Outer Joins

A left outer join produces all records of the child table, also called the right table. The records of the child table that don't have an entry in the foreign key column are marked as NULL.

To create a left outer join, you can replace the TypeOfJoin factor of our formula with LEFT JOIN or LEFT OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
             Genders.GenderID, Genders.Gender
FROM Persons
LEFT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID

This would produce:

Notice that the result includes all records of the Persons (also called the right) table and the records that don't have an entry in the GenderID column of the Persons (the right) table are left empty.

To create a left outer join in the Data In Table window, after establishing a relationship between both tables, you can right-click the line that joins them and click Join Properties:

In the Join Properties dialog box, read and click the 2 radio button and click OK:

This would change the join into a left join.

Practical Learning: Using Left Outer Joins

Right Outer Joins

A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in this case the Genders table) and shows each record of the child table (in this case the Persons table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Persons records that have a 1 (Female) value for the GenderID column. After the first record, the right outer joins moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table.

To create a right outer join, you can replace the TypeOfJoin factor of our formula with RIGHT OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
             Genders.GenderID, Genders.Gender
FROM Persons
RIGHT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID

This would produce:

Notice that the query result starts with the first record of the parent table, also called the left table (in this case the Genders table), and lists the records of the child table, also called the right table (in this case the Persons table), that have the entry corresponding to that first record. Then it moves to the next GenderID value.

To create a right outer join in the Select Query window, after establishing a relationship between both tables, you can right-click the line that joins them and click Join Properties. Then, in the Join Properties dialog box, click the 3 radio button:

And click OK

Practical Learning: Using Right Outer Joins

1. Replace the word LEFT with RIGHT
    
   
2. Right-click the title bar and click Query Design
    
   
3. Right-click the line between the tables and click Join Properties
    
   
4. Click OK
5. Right-click somewhere in the window and click Datasheet View
6. Right-click the title bar of the window and click Query Design
7. Double-click the line between both lists
8. In the Join Properties dialog box, click the 2 radio button and click OK