What Is A Join Part 5: Outer Joins

Outer Join (Left Join or Right Join)

So far in this series (What Is A Join: Part1, Part2, Part3, and Part4), I've concentrated on the Inner Join, which is the most common type of join. However, the Inner Join has a limitation. It will only match field values that exist in both tables. Going back to our Order and OrderDetails tables:

Orders
OrderID	OrderNumber
1	111
2	222
3	333
4	444

You can see that there is an OrderID "4" in the Order table.

OrderDetails
OrderDetailID	OrderID	Quantity
1	1	1
2	2	2
5	3	3
6	1	2
7	1	1
8	3	2
9	3	1

However, there are no records for OrderID "4" in the OrderDetails table.

Query1
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

Thus, OrderID "4" does not appear in the resulting Inner Join. But there times when you want to show all of the records of one table and the associated records in another, whether or not all of the records in the first table have a match.

This is called an Outer Join. In Access, there are two types of Outer Joins, Left Join and Right Join. The Left and Right refer to which side of the equal sign the table is on in the JOIN clause. I'll get back to that in a minute.

To create an Outer Join in the Query Builder, start with a standard Inner Join. Then right click on the Join line, and select Join Properties You'll get a pop-up box with three choices:

Selecting either Option 2 or 3, will create an Outer Join. If you select 2, you'll be creating a Left Join. If you select 3, you'll be creating a Right Join. The result will look like this:

And the resultset will look like this:

LeftJoin
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1
444

The SQL for this query looks as follows:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders LEFT JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;

The "Left" and "Right" handedness doesn't really have anything to do with the table positions in the Query Builder. It has more to do with the positions of the tables with respect to the JOIN keyword.

An Inner Join will look like this:

FROM Order JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

Since it is joining only matching records, it doesn't matter what order the tables appear in the clause. But to create an Outer Join, you have to indicate which table will show all records. In Access, you do that with LEFT JOIN and RIGHT JOIN.

FROM Order LEFT JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

In a Left Join, the table on the left of the JOIN keyword (Order) will show all records.

FROM Order RIGHT JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

In a Right Join, the table on the right of the JOIN keyword (OrderDetails) will show all records.

Multiple Joins with Outer Joins

An outer join (left or right) can participate in a multiple table join only under circumstances. If the query also involves an equi-join and certain outer joins, it will result in the Ambiguous Outer Join error. For more information about this error, see my post: What is an Ambiguous Outer Join?

One last thing about Outer Joins: Earlier, I discussed creating an Equi-Join in the Where clause, but as far as I know, in Access you can't create an Outer Join that way.

Next time, I'll look at a specific application of the Outer Join: the Unmatched Query.