Microsoft Access - OUTER JOIN Overview

An outer join is another kind of join and is the second most common join (inner join being the most common). Unlike an inner join, an outer join will give you data even if the common field that you select does not have a value that is the same in both tables. There are two types of outer joins. A left outer join (also called left join) and a right outer join (also called right join).

Visualize the two tables you want to join, pick the one for which you want to see all the data for. When you call a left join, that table will need to be on the LEFT of the actual left join statement, and when you call a right join, that table will need to be on the RIGHT of the actual right join statement. left and right joins are merely names. I always use left join because it is easier for me to visualize. You could just as easily use a right join and just swap the position of the tables.

So if you read the previous reading on the inner join, you realize that when someone in the "People" table has a favorite game that is not listed in the "Video games" (With no spaces and the G letter capitalized. See note above) table, that person will not show up in the final result. This is different with an outer join. For example with a left outer join, you will get all the data from the left table but only data from the right table where the common field has a value that is the same in both tables (rows where the common field does not have a value in the right table will show up as NULL)

Using our "People" and "Video games" tables for another example to show you more clearly what this means, imagine the "People" table having 2 people with the following values for the fields we are interested in ("Video game ID" (With no spaces and the letter G capitalied. See note above) is the ID of that person's favorite video game):

ID: 1

FirstName: Jack

VideoGameID: 1

and

ID: 2

FirstName: Joe

VideoGameID: 2

The "VideoGames" table has the following values for the fields we are interested in:

ID: 1

Name: Gears of War

If you were to join the two tables on the "People.VideoGameID" field and the "VideoGames.ID" field, you will get 2 results back if this join is an outer join.

Notice how Jack's favorite video game with the ID of 1 is in the "Video games" table, called Gears of War. However, poor Joe's favorite video game is not in the "Video games" table (someone forgot to update the "Video games" table with all video games).

If we join the two tables and show just the person's name and the name of their favorite video game using an outer join, we would get this result:

Jack, Gears of War

Joe, Null

Had we done an inner join, we would only have gotten 1 row:

Jack, Gears of War

This is the most important thing to understand about how an outer join works, that you will get all the values from one and NULL values from the other table if that table doesn't have a corresponding row with the same value in the selected common field(s).

If you want results only where a person's favorite video game exists in the "Video games" table, you would use an inner join, if you wanted results for all the people regardless of whether or not their favorite video game exists in the "Video games" table, you would use an outer join.