| Lesson 7 | Specifying join properties for queries |
| Objective | Identify three different types of joins available and how they can be used. |
Specifying Join Properties for Access Queries
Inner Join is default for Access
- Inner Join
- Left Outer Join
- Right Outer Join
When you create a query that uses a join between tables, Access will create the join as an inner join by default. This means that the tables on both sides of the join must have matching records in order for those records to be displayed.
There are two other types of joins as well, left outer and right outer joins.
To specify the type of join being used, double-click on the join line between the two tables. When you double-click, the Join Properties dialog box appears, as shown here:
To specify the type of join being used, double-click on the join line between the two tables. When you double-click, the Join Properties dialog box appears, as shown here:
Let us take a closer look at the three types of joins shown above, as used with the 'Projects' and 'Hours' tables.
Inner join
Only include rows where the joined fields from both tables are equal.
If a project entered in the 'Projects' table does not have any hours assigned to it in the 'Hours' table, that project will not be displayed.
If a project entered in the 'Projects' table does not have any hours assigned to it in the 'Hours' table, that project will not be displayed.
Left outer join
“Include ALL records from 'Projects' and only those records from 'Hours' where the joined fields are equal.”
Left outer joins are used when you want to display all records from one of the tables regardless of whether there is a corresponding entry in the other table used. For example, if you want to see all the projects regardless of whether any hours have been entered, you would use a left outer join. When you specify a left outer join, you will see an arrow pointing toward the second table in the join, as seen here:
Left outer joins are used when you want to display all records from one of the tables regardless of whether there is a corresponding entry in the other table used. For example, if you want to see all the projects regardless of whether any hours have been entered, you would use a left outer join. When you specify a left outer join, you will see an arrow pointing toward the second table in the join, as seen here:
Right outer join
“Include ALL records from 'Hours' and only those records from 'Projects' where the joined fields are equal.”
Right outer joins are the same as left outer joins, just flipped around. They are used less than the other two joins because you can specify the tables in the order you desire and use a left outer join. Using a right outer join with the 'Projects' and 'Hours' tables, you can see which records are in 'Hours' that don’t have a legitimate projects associated with them. Right outer joins are good for checking out data integrity. When you specify a right outer join, the arrow points to the first table in the join, as shown here:
Right outer joins are the same as left outer joins, just flipped around. They are used less than the other two joins because you can specify the tables in the order you desire and use a left outer join. Using a right outer join with the 'Projects' and 'Hours' tables, you can see which records are in 'Hours' that don’t have a legitimate projects associated with them. Right outer joins are good for checking out data integrity. When you specify a right outer join, the arrow points to the first table in the join, as shown here:
In the next lesson, you will learn about the different types of queries and where you can use them.
Specifying Join Properties For Queries - Exercise
Click the Exercise link below to practice using a left outer join to create a query.
Specifying Join Properties For Queries - Exercise
Specifying Join Properties For Queries - Exercise