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.
Establish an Outer Join
To establish an outer join, you must modify the join between the tables included in the query:
1. | Double-click the line joining the tables in the query grid.
|
2. | The Join Properties window appears (see Figure 1).
Select the type of join you want to create. To create an outer join
between the tables, select Option 2 or Option 3. Notice in Figure 1
that the description is Include All Records from Orders and Only Those
Records from Order Details Where the Joined Fields Are Equal.
|
3. | 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 this change is made looks like this:
SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
You can use an 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, simply enter Is Null as
the criteria for any required field on the many side of the join. A
common solution is to place the criteria on the foreign key field. In
the query shown in Figure 2, only customers without orders are displayed in the query result.
