Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Outer Joins

You use outer joins 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, whether or not they have orders. This is when an outer join is necessary.

NOTE

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 you are enforcing referential integrity.

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

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

SELECT DISTINCTROW tblClients.ClientID, tblClients.CompanyName
FROM tblClients
LEFT JOIN tblProjects ON tblClients.ClientID = tblProjects.ClientID;

A left outer join can also be used 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 11.30, only clients without projects are displayed in the query result.