Working with Outer Joins

Outer joins are used when you want the records on the "one" side of a one-to-many relationship to be included in the query result, regardless of whether there are matching records in the table on the "many" side. With a Customers table and an Orders table, for example, users often want to include only customers with orders in the query output. An inner join (the default join type) does this. In other situations, users want all customers to be included in the query result, regardless of whether they have orders. This is when an outer join is necessary.

There are two types of outer joins: left outer joins and right outer joins. A left outer join occurs when all records on the "one" side of a one-to-many relationship are included in the query result, regardless of whether any records exist on the "many" side. A right outer join means all records on the "many" side of a one-to-many relationship are included in the query result, regardless of whether there are any records on the "one" side. A right outer join should never occur if referential integrity is being enforced because all orders should have associated customers.

To establish an outer join, you must modify the join between the tables included in the query:

Double-click the line joining the tables in the query grid.
The Join Properties window appears (see Figure 15.31). Select the type of join you want to create. To create a left outer join between the tables, select Option 2. Select Option 3 if you want to create a right outer join. Notice in Figure 15.31 that the description is Include ALL Records from Customers and Only Those Records from Orders Where the Joined Fields Are Equal.

Figure 15.31. Establishing a left outer join.
Click OK to accept the join. An outer join should be established between the tables. Notice that the line joining the two tables now has an arrow pointing to the "many" side of the join.

The SQL statement produced when a left outer join is established looks like this:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

You can use a right outer join to identify all the records on the "one" side of a join that don't have any corresponding records on the "many" side. To do this, you simply enter Is Null as the criterion for any required field on the "many" side of the join. A common solution is to place the criterion on the foreign key field. In the query shown in Figure 15.32, only customers without orders are displayed in the query result.