Chapter 4: Selecting Data with Queries
165
Understanding Table Join Types
In Chapter 3, you learned about table relationships and relating two tables by a common field.
Access understands all types of table and query relations, including:l One-to-onel (^) One-to-many
l Many-to-one
l (^) Many-to-many
When you specify a relationship between two tables, you establish rules for the type of relation-
ship, not for viewing the data based on the relationship.
To view data in two tables, they must be joined through common fields in the two tables. Tables
with established relationships are automatically joined through the relationship. Within a query,
you can create ad hoc joins or change existing joins, and as you’ve already seen, Access often auto-
joins tables for you. Just as there are different types of relationships, there are different types of
joins. In the following sections, you learn about a number of different types of joins:
l (^) Inner joins (equi-joins)
l Outer joins
l (^) Self-joins
l Cartesian (cross-product) joins
Working with inner joins (equi-joins)
The default join in Access is known as an inner join or equi-join. It tells Access to select all records
from both tables that have the same value in the fields that are joined.
Note
The Access manuals refer to the default join as both an equi-join and inner join (commonly referred to as an inner
join in database relational theory). The Access Help system refers to it as an inner join. The terms equi-join and
inner join are interchangeable; however, in the remainder of this chapter they are referred to as inner joins.
If records are found in one table that don’t have matching records in the other table, they’re
excluded from the returned recordset and aren’t shown in the datasheet. Thus, an inner join
between tables is simply a join where records are selected when matching values exist in the joined
field of both tables.
You can create an inner join between the tblCustomers and tblSales tables by bringing these
two tables into a new query and clicking on the join line to activate the Join Property dialog box
and selecting the first choice: Only Include Rows Where the Joined Fields from Both Tables Are
Equal.