Sometimes in your queries, you want to see all the records from one table, along with the matching records from another table. To do this, create outer joins.
Inner joins only display data when there is a matching ID in both connected data sources. For example, if you want to display the products and quantities sold, you create an inner join between the Products and Order Details tables. When you run the query, only products that occur in orders, and thus appear in both tables, are returned.
In contrast, outer joins return all the data from one data source and some of the data from another table or data source.
For example, if you want to see which products haven’t sold, you create a query that shows the Product ID from the Products table and any matching Order IDs from the Order Details table. Products that don’t have matching records in the Order Details table return the product name with no value for the Order ID field.
Left outer joins and right outer joins
In an outer join, the table that displays all its records is called the base table. The table that displays only some (matching) records is called the secondary table.
The Join Properties dialog box describes what you see with different types of joins:
Suppose you’ve created a query to show which products your customers bought. Your output fields are Product Name, Order ID, and Quantity.
Inner join. Only include rows (records) in which the joined fields from both tables are equal, for example, only the products that have orders associated with them.
Left outer join. Include all records from the table under Left Table Name and only those records from the table under Right Table Name in which the joined fields are equal, for example, all orders, but only the quantities and Product IDs for those orders that have products associated with them.
Right outer join. Include all records from the table under Right Table Name and only those records from the table under Left Table Name in which the joined fields are equal, for example, all the products, even when there are no orders for them, in which case blank values appear in the Order ID and Quantity fields.
As you can tell from these examples, it’s important to know which table is on the left and which is on the right, which can actually be different from how the tables are arranged in the query window. To understand which data you’ll get from your query, read the text carefully in the Join Properties dialog box.
1. Add data sources
Select Create > Query Design.
Add all the tables and queries you need as data sources. (Don’t worry. If you forget one, you still have an opportunity to add more data sources later.)
If it detects or deduces them, Access may show relationships for the data sources.
2. Add a join
If you don’t see a relationship between the sources, add a join. Drag from the field you want in one data source to the corresponding field in another data source. The table you drag from is the table that will appear on the left in the Join Properties dialog box. By default, Access creates an inner join. If you need an outer join, change its type.
3. Change a join
Double-click the line representing the join you want to change.
In the Join Properties dialog box, to create an outer join, select the second or third option. Then, select OK.
Remember to pay attention to which table is the base table—the one from which all records are displayed.
4. Complete the query
Follow the usual steps for creating a query: add output fields, add any criteria, and run or save your query. To learn how, see Create basic queries.
Let’s look at how to use left and right outer joins in a Select query.
Outer joins have sides—left and right—and they return ALL the data from one table, and only MATCHING data from the other. They are useful when you want to know when something has NOT happened; for example, a certain item is NOT selling, or students are NOT registering for a certain class.
So now let's add just a little bit to this.
The table that returns ALL data is called the "base table" and the table that returns MATCHES is called the "secondary table."
Which is the base and which is secondary table depends on the direction of the join.
If you use a right outer join, the table on the right is the base table. If you use a left outer join, the table on the left is the base table.
You control whether you’re using a left or right outer join in the Join Properties dialog box. Select and then either right-click or double-tap the join line… then Join Properties… then select the second or third option.
So, let's put this to work:
On the Create tab, select Query Design. Here we’ll add the OrderDetails and Products tables.
You can see that Access used a one-to-many relationship as a PATTERN for the join, and that means it's an inner join.
We add the Product Name and Quantity fields, and if we run the query, it only returns data for products that have been ordered.
We know this, because if the query were returning data for unordered products, we'd see blank records here.
Back to Design view, we right-click or double-tap the join line and select Join Properties. Select the second option and notice the join line.
The arrow tells you it's an outer join, and it also points to the secondary table. Run the query, sort the data from smallest to largest, and now you know which products are not selling.
So what happens if we change to a right outer join?
We make the change, … run the query, … and get the same results as an inner join: no blank records.
If we look at the Join Properties dialog box, we see why.
We're returning all the data from the Order Details table, but only MATCHES from the Products table. In other words, we're only seeing products that have a corresponding order and that's the same result returned by an inner join.
Now you’ve seen how to use outer joins in a Select query that uses multiple data sources. So, think your joins through, and if you don't get the result you want, you can always change them.