databasedev.co.uk - database solutions and downloads for microsoft access

Query Joins - Inner & Outer Joins

You will often need to join tables in a Microsoft Access query where you need to view information from two or more separate database tables. For this you will need to use query joins to accomplish the task.

When you run a database query to find data in related tables, by default Microsoft Access will look for records that have a matching value on both sides of the relationship. Whilst this is what you may want to do most of the time, by knowing how to use the different types of query joins, you can control which records will be displayed as the output. This enables you to again find the exact data that you want - not more and not less.

Query Joins

A join is a temporary relationship that you can create between two tables in a database query that do not already have an established relationship or common field with the same fieldname or data type. Database tables that are joined in a query are related in that query only, and nowhere else. The type of join that you use indicates which records the query will select or perform the chosen actions on.

Note: Creating a query join will not establish a permanent relationship between the tables. Permanent relationships can only be created in the Microsoft Access relationships window.

Inner Join

Definition: An inner join is a join that selects only those records from both database tables that have matching values. Records with values in the joined field that do not appear in both of the database tables will be excluded from the query. One or more fields can serve as the join fields.

• The inner join is also known as an equi-join.
• The inner join is the default join type in Microsoft Access

The above shows a conceptual diagram of the inner join between Customer data and Order data.

Analogy: Consider a business that employs both managers and engineers - and some employees that are both. An inner join is like a union of this set; it selects the set of people that are both managers and engineers and provides information about them in both roles.

Outer Join

Definition: An outer join selects all of the records from one database table and only those records in the second table that have matching values in the joined field. In a left outer join, the selected records will include all of the records in the first database table. In a right outer join, the selected records will include all records of the second database table. One or more fields can serve as the join fields.

Left Outer Join example:

The above conceptual diagram details the Left Outer Join between Customer data and Order data

Analogy: Consider again the business that employs both managers and engineers. A left outer join selects all of the managers, providing the information about them, but in the case of managers who are also engineers, it provides additional information about them.

Right Outer Join example:

The above shows a conceptual diagram of a Right Outer Join between the Customer data and the Order data.

Analogy: Consider again the business that employs both managers and engineers. A right outer join selects the set of all engineers, providing information about them, but in the case of engineers and also managers, if provides additional information about them.

Changing the Query Join Type

The Join Properties dialog box enables you to specify how two tables are to be joined in a Microsoft Access query. The three options that it includes describe which records you want the query to select. Option 1 in the dialog box is the inner join (the default in Microsoft Access).

Options 2 and 3 represent outer joins. Read the table names carefully when selecting these joins: if the join line was drawn starting from the table on the left, the second option represents the left outer join and the third option will represent a right outer join.

In a traditional database diagram, the "one" or "primary" table is usually drawn to the left of the "many" or "secondary" table.

In this case, a left outer join includes all records from the table on the "left side", and the right outer join includes all records from the table on the "right side".