Retrieve related records from multiple tables with joins

Microsoft Query

Show All Show All

Retrieve related records from multiple tables with joins

This topic provides information about the following:

Joining tables

Inner joins

Outer joins

Self joins

Full outer joins

Subtract joins

Joining tables

Databases consist of multiple tables of related information. When you add two (or more) tables to a query, Microsoft Query automatically creates a "join" between the tables when it recognizes a primary key field in one table and a field with the same name in the other table. Join lines show the relationships of the data in the tables.

Join lines between tables

Join lines connect the Products table to the Order Details table by joining the ProductID field in each table. The Order Details table and the Orders table are also joined by their OrderID fields.

There are two basic types of joins that can be used to retrieve records from multiple tables. An inner join retrieves only related records from the tables. An outer join retrieves all of the records from one table and related records from another table.

In addition to inner and outer joins, there are three other types of joins you can create— self, full outer, and subtract joins. A self-join returns a result set that combines information contained in one table. A full outer join returns all of the records from both tables, even if the records are not related. A subtract join returns only those records from the first table that do not match records in a second table.

Return to top

Inner joins

An inner join— the default type of join— retrieves only records that have the same value in the joined fields in both tables. Microsoft Query combines the matching records from each table and displays them in the result set.

Example of an inner join

More information

Join tables in a query

Change the type of join between tables in a query

Return to top

Outer joins

An outer join retrieves all of the records from one table and any matching records from another table. When values in the two tables are the same, Microsoft Query combines the matching records and displays them as one record in the result set. When a record from the table that's contributing all of its records can't be matched with a record from the other table, the record still appears in the result set. However, Query displays empty cells where no matching record was present in the other table.

Example of an outer join

More information

Join tables in a query

Change the type of join between tables in a query

Return to top

Self joins

When you want to combine information in one table, use a self-join. For example, in a table of employees, each record includes the employee ID for the manager that an employee reports to. Because the manager is also in the same table as the employee, you can join the table to itself to return both the name and the employee ID of the manager.

Example of a self-join

By joining the table to itself, you can retrieve both the ID and the name of the manager.

More information

Retrieve multiple fields with a self-join

Return to top

Full outer joins

When you want to combine all of the data from two tables, you can create a full outer join query. This type of query returns all of the data from both tables, even when the data is not related.

Tables with similar data before a full outer join

For example, if you have a Customers table and an Orders table, you can use a full outer join to return all of the orders that are pending (from the Orders table) and all of the customers who have not placed an order (from the Customers table).

Result set showing customers who have not placed orders

The first two names in this list have not placed orders.

Note  When you create a full outer join, Query displays only the result set of the query, not the tables used within the query.

More information

Combine all the data from two tables

Return to top

Subtract joins

When you have two tables that have similar data, such as two tables of customers, and each table has some information that is not included in the other table, you can create a subtract join. A subtract join returns only the records from the first table that do not match the records from the second table.

Tables with similar data before a subtract join

Two tables with similar customer data

Example of a subtract join

More information

Retrieve records that two tables do not have in common

Return to top