We have seen how relationships are used to connect tables based on a common join field and how records are displayed in a query. Data must match certain rules and conform to certain criteria to be included in the query result set.
Setting the join properties, also known as join type, will determine what records will be displayed when the query is executed. There are three relationship join types available in relational databases:
- Inner join
- Left outer join
- Right outer join
Let's explore these different join types, but first, let's save our query with a new name.
Step1. To save the query with a new name, press:
F12 key, type: qryCoursesSectionsAndAllFacultySummer18,
Step2. Switch to Design view.
Step3. To access the join properties for the relationship between tblFaculty and tblSections,
Introducing Inner Join
A query using an inner join only returns records where data exists on both sides of the relationship. For example, we would see only instructors who are teaching sections, and sections with assigned instructors. This is the default join type in Access. It is also the most common type of join in relational databases. Option 1 in the Join Properties dialog box is always an inner join.
In an inner join, a common EmployeeID/InstructorID value that exists in the joined field of both tables will be included in the query results. The primary key value in the primary table must also exist in the foreign key field of the related table in order for both the faculty member and the section they are teaching to appear.
We do not see faculty who are not teaching during the summer, nor do we see sections that have yet to be assigned an instructor in the results of our current query.
The following diagram illustrates an inner join in which the shaded area represents records that are matched on either side of the join:
Next we will explore a different type of join.
Introducing Left Outer Joins
What if we wanted to see all faculty, whether they are teaching during the summer or not? There is a strong possibility that we wouldn't even realize that those records were missing from this result set unless we had carefully examined the records. Suppose some faculty are dedicated to research only during the summer, but we still need to see their names in the results. How could we get the answer to this question?
A left outer join type is option 2 in the join properties dialog box and will return all records from the primary table and those records from the related table that have matching key values. For example, a left outer join would return every faculty member in tblFaculty and if the faculty member is teaching a section, the information for that section.
NOTE: You may remember that orphan records are not allowed when referential integrity is established, since the foreign key value in the related table must match a primary key value listed in the primary table; however, since empty values are allowed, there could be sections which have null values in the InstructorID field, and therefore, are without instructors.
The following diagram illustrates a left outer join in which the shaded area represents all faculty and any related sections:
We will change the join type to a left outer join in order to view all faculty. This will allow us to see who is teaching and who is assigned to research during the summer.
Step1. To make a left outer join, in the join properties dialog box,
Step2. Save qryCoursesSectionsAndAllFacultySummer18.
Step3. To view the query in datasheet view, on the Ribbon,
Step4. To close the error dialog box,
Understanding Ambiguous Joins
An ambiguous outer join is a join that Access cannot make because key values on either side of the join do not match. To illustrate this problem, let's look at how Access is building this query. Access, like other relational databases, builds queries step-by-step, not all at once.
Let's look at this through the lens of qryFacultyCoursesSectionsSummer18.
Step1. Open qryFacultyCoursesSectionsSummer18 in Design view.
NOTE: For the rest of this discussion, we will refer to tblSections's composite key as SectionKey.
Step2. Close qryFacultyCoursesSectionsSummer18.
Step3. Open tblFaculty in Datasheet view.
Step4. To see the sections that Lorujama Barraza is teaching, to the left of the Employee ID Number,
Step5. To find Dina Thibodeaux, in the Search box at the bottom of the table, type:
Step6. To view Diana Thibodeaux's subdatasheet, to the left of her Employee ID Number,
Step7. Close tblFaculty.
- Build the first result set by joining tblSections and tblCourses according to the properties of the join defined in the query designer. This creates a result set that represents all sections and any matching course information (result set 1).
- Build the second result set by joining tblFaculty and tblSections according to the properties of the join defined in the query designer. This creates a result set that represents all faculty and matching sections (result set 2).
- Join the result set from the first join with the result set from the second join. This creates a result set that represents all courses, matched to their course sections, and all faculty (final result set).
Introducing Right Outer Joins
In order to see all results from our second result set (tblFaculty and tblSections), we need to configure the first result set (tblCourses and tblSections) to allow SectionKey values to be empty. To accomplish that, we need to use a right outer join between tblCourses and tblSections.
A right outer join returns all records from the related table and those records from the primary table that have matching key values. This type of join is labeled as option 3 in the join properties dialog box and accomplishes the opposite of a left outer join. For example, we will see all sections in tblSections, which includes nonexistent sections. In addition, if the section has been assigned a course, the course information from tblCourses.
The following diagram illustrates a right outer join in which the shaded area represents all sections and the related course information.
We will change the join type to a right outer join, which will allow us to view all sections.
Step1. To modify the join properties between tblSections and tblCourses,
Step2. To select the right outer join, in the join properties dialog box,
NOTE: As a general rule, when using outer joins, you should be able to follow the join arrows in the same direction through each table involved with the query. This is a good indication that you do not have any ambiguous outer joins that need resolving.
Step3. To view the query results in datasheet view, on the Ribbon,