Understanding Join Properties

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, press: Enter key

Let's open the join properties for the relationship between tblFaculty and tblSections.

Step2. Switch to Design view.

Let's say we want to view all faculty whether they taught in the summer or not. To see the non-teaching faculty, we will have to change the join type of the relationship between tblFaculty and tblSections.

Step3. To access the join properties for the relationship between tblFaculty and tblSections,

Double-Click

The Join Properties dialog box appears:

These join properties can also be set in the Relationships window when defining relationships between tables. However, since join types affect query results, it is more practical to set these properties within the query itself.

We will see how various join types affect query results.

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,

Click

Step 2. To accept the changes we've made,

Click 

In the query designer, you should see a change in the join line between tblFaculty and tblSections:

The arrow indicates that we want to include all records from tblFaculty (the tail of the arrow) and match them with records from tblSections (the head of the arrow).

We've now included all faculty in our results. Let's save and view the query.

Step2. Save qryCoursesSectionsAndAllFacultySummer18.

Step3. To view the query in datasheet view, on the Ribbon,

Click

Access returns an error:

A query that uses an outer join will not always generate an error. This query does because of the data structures involved, which we will explore in a moment. Let's close the dialog box and then examine why this error is occurring.

Step4. To close the error dialog box,

Click

Let's see why this error occurs with this query, qryCoursesSectionsAndAllFacultySummer18, and how to resolve it. First, we'll need to understand how Access builds queries in more detail.

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.

We can see the design of this query:

This query involves joins between three tables. Databases cannot join three tables simultaneously; however, they can join two tables twice and then join the results of those two joins together. For example, in this query, Access will first join together tblCourses and tblSections with an inner join, returning a result set of scheduled courses that have a section:

Next, in creating qryFacultyCoursesSectionsSummer18, Access will join together tblFaculty and tblSections with an inner join, returning a result set of sections and their assigned faculty members:

Finally, in creating qryFacultyCoursesSectionsSummer18, Access will join the two results sets on a common key field. In this case, the key that both result sets have in common is the composite key of SemesterYear and SectionNumber from tblSections.

NOTE: For the rest of this discussion, we will refer to tblSections's composite key as SectionKey.

In qryFacultyCoursesSectionsSummer18, nothing causes an error because the query only uses inner joins. If any record in any of the involved tables does not have a matching record in the other, it's left out of the final results as well as the two initial result sets.

Let's explore how qryCoursesSectionsAndAllFacultySummer18 is different.

Step2. Close qryFacultyCoursesSectionsSummer18.

You are returned to qryCoursesSectionsAndAllFacultySummer18.

The query we are building closely resembles qryFacultyCoursesSectionsSummer18. The only difference is that we want to see all faculty members. The first result set is the same:

Just like in qryFacultyCoursesSectionsSummer18, Access first joins together tblSections and tblCourses. The second result set is where the similarities between the two queries end:

Here's where the ambiguous outer join problem starts. The problem is being caused by SectionKey values included in result set 2 not having a matching SectionKey values in result set 1. This is because not all faculty members are assigned to a section for summer 2018. Currently, Access is missing information about how to treat these faculty members.

Let's look at the data returned in both of these result sets more closely to see exactly what SectionKey from result set 2 is not matched in result set 1. Knowing this will help us resolve the error we saw earlier when we first created qryCoursesSectionsAndAllFacultySummer18. We can see this by examining data related to faculty members.

Step3. Open tblFaculty in Datasheet view.

You see tblFaculty.

Notice the plus signs to the left of the data. These indicate that Access has created a subdatasheet. A subdatasheet is a way to view a record's related data from another table. In the case of tblFaculty, the related data comes from tblSections. We can use the associated subdatasheets to explore this data. First, let's look at a faculty member who is teaching in summer 2018. The first faculty member in tblFaculty, Lorujama Barraza, is teaching a course during that semester.

Step4. To see the sections that Lorujama Barraza is teaching, to the left of the Employee ID Number,

Click [+]

The subdatasheet is expanded and we see that Lorujama is teaching during the summer semester of 2018, as well as other semesters:

The section of interest in this case is BIOL-L101, section 1011 in Summer 2018. The SectionKey for this section would appear in both result set 1 and 2. It's included in result set 1 because it is assigned to a course, BIOL-L101. It appears in result set 2 because it is assigned an instructor, Lorujama Barraza.

Let's switch our focus to another instructor, Diana Thibodeaux. First, let's find her record in the table and then view its subdatasheet.

Step5. To find Dina Thibodeaux, in the Search box at the bottom of the table, type:

Thibodeaux

Diana's last name will be highlighted. Let's look at her subdatasheet.

Step6. To view Diana Thibodeaux's subdatasheet, to the left of her Employee ID Number,

Click [+]

We can see that Diana's subdatasheet is empty:

An empty subdatasheet here indicates that the SectionKey value associated with Diana's record in result set 2 is empty. Remember that since we're using a left outer join, all faculty members will be visible in result set 2. If a faculty member does not have an assigned section, as is the case for Diana, all of the section information for that record will be empty, including the SectionKey.

This may not seem like a problem at first. Remember that the next step Access takes is joining together result sets 1 and 2. When Access tries to make this join, it will set the SectionKey for each record in result set 1 equal to the SectionKey for each record in result set 2.

Since result set 1 is made with an inner join, there are no empty SectionKey values. Since there are no empty SectionKey values, Diana Thibodeaux's empty SectionKey has no match in result set 1.

Let's close the table and look at why the empty subdatasheet causes problems.

Step7. Close tblFaculty.

You are returned to qryCoursesSectionsAndAllFacultySummer18.

Let's look at a graphical representation of where Diana's record lies in our datasets. The shaded area represents the join that Access is trying to make between result sets 1 and 2:

Since Diana has not been assigned to teach and therefore has no associated SectionKey, she cannot be matched with a record in result set 1. However, since she meets the criteria for result set 2 just by being a faculty member, she must be included in the final results. This is what Access means by "ambiguous outer join." There is not enough information in the query we have created to cleanly match both result sets.

To resolve the ambiguous outer join, we need to allow result set 1 to contain empty SectionKey values. Conceptually, the SectionKey would be empty if there is a course that has not been assigned a section. Therefore, we must change the join type between tblCourses and tblSections to an outer join that includes all courses and their matching sections, if any exist:

As you can see, if empty section values are allowed in result set 1, Diana's record is allowed to exist in the final result set.

In summary, here's how Access builds a query which includes all faculty who are not assigned to teach a section:

1. 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).
2. 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).
3. 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).

In the final result set (C), if the faculty member is teaching, they will be matched to their related section and course information. If they're not teaching, they will be matched to empty values for all section and course fields.

Now that we understand how Access builds query results, we need to change the join properties of the relationship between tblCourses and tblSections to allow courses that do not have assigned sections to appear in the 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,

Double-Click

The Join Properties dialog box appears:

Let's change this to a right outer join.

Step2. To select the right outer join, in the Join Properties dialog box,

Click, Click

You should see:

Let's view the results now.

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,

Click

While scrolling through the 49 results, you might realize that there are no faculty here who are not teaching in the summer. For instance, even though we would expect Diana Thibodeaux in our records, she does not appear. This is because we are only including courses where the SemesterYear is set to SU18. We are not allowing results with no value (i.e. faculty with no teaching assignments) to appear and this is forcing Diana Thibodeaux to not appear in the result set.