Retrieve related records from multiple tables with joins
This topic provides information about the following:
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 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.
More information
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.
More information
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.
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.
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).
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.
Two tables with similar customer data
More information
Retrieve records that two tables do not have in common
Return to top