Microsoft Access – INNER JOIN Overview

Note: Due to editor’s requirements, all of the “INNER JOIN” statements which are customarily written in all caps are going to be spelled in all lower case “inner join” in the this article. Also, table names that have the first letter of a word in the name capitalized will have only 1 letter capitalized. For example a table called “VideoGames” is considered best practice, but for the content part of this tutorial, it shall be written as “Video games”. (the space in the table name is bad, do not have a table name with a space, the space in the table name in the content parts of this tutorial are there stricly for “technical reasons” to meet article requirements.)

In this article, I will talk about the basic concept behind an inner join. The inner join is the most common type of SQL join. In a later tutorial, I will talk in more detail about the actual SQL to perform an inner join using Microsoft Access SQL statements.

An inner join can most simply be defined as accessing or showing data from two different tables by joining the two tables on one or more common fields.

As an example, I will use the two tables created in a previous reading where one of the tables is called “People”. This table has information about different people. The second table is called “Video games” (without the space and with the G letter capitalized. Refer to note above). This table has information about different video games.

The “People” table has the name of the person (along with other information related to the person), but most notably, it has a field for the person’s favorite video game in the form of a field called “Video game ID” (No spaces, with the G letter capitalized. Refer to note above) which represents a favorite video game for that person.

The “Video games” table has a column called ID along with other columns that represent information about that video game.

You should realize that the common field between the two tables is the field called ID in the “Video games” table and the field called “Video game ID” in the “People” table. When we do an inner join on these two tables we would use those two fields in the respective tables.The inner join will result in the records where the ID in the “Video games” table matches with the “Video game ID” in the “People” table.

For example, if the “People” table has someone named Jack whose favorite video game is “Gears of War”, with a Video game ID = 3, (in the “People” table) then in the “Video games” table, wherever the ID = 3 (in the “Video games” table), that will be the record that is displayed from the “Video games” table. In that example, the inner join was done between the “Video game ID” field in the “People” table and the ID field in the “Video games” table.

However, if ID = 3 does not exist in the “Video games” table, which means that the favorite video game of Jack does not exist in the “Video games” table, then that record will not show up in the final result of the inner join. This is the most important thing to know about how an inner join works.

The common field that you use for the inner join needs to have the value that is the same in those fields for the final inner join query to show you a result.

Usually you would use an inner join to show data that is a specialized among two tables. The most important thing that you will need to know when you want to do an inner join is figuring out which common field to use to perform the inner join. For our example, it was easy to see that it was the “Video game ID) field in the “People” table and the ID field in the “Video games” table.