There are both inner joins and outer joins. Inner joins, which are also known as equi-joins, are by far the most common. All the queries you have done used an inner join. An inner join retrieves records only when there are equal values (that is, matching data) in the common field in the tables. As you saw, if you have suppliers in one table and merchandise in another, and a common SupplierID in both, you can use an inner join to create a query that matches merchandise data with supplier data. When you create a multitable query, an inner join is the default. If you want to create queries in which only records with matching data are retrieved, you don't have to worry what type of join you're using.
What are outer joins? In an outer join, all the records from one table and only those records from the other table in which there's matching data are retrieved. Because all the records are retrieved from one table, you can find which of them don't have matching records in the other table.
Outer joins work nicely in auditing and other exception-to-the-rule tests that ask “What's wrong with this picture?” questions. For example, if a firm is included in the customers table, why don't you see any orders from them? Or imagine the case of a supplier with no products. If a firm is truly a designated supplier, why aren't any items from its product line in your inventory?
In most cases, there will be entirely reasonable explanations: Customers might be approved well before they place any orders; the firm might only occasionally use many of its varied suppliers. But these are the type of questions outer joins can answer.
Suppose Nifty Lions's owner wants to see if there are any customers without any orders. You'll create a multitable query using an outer join to find these records.
Q&A
|
To compare an inner with an outer join, run the query as you would usually do.
1. | Close the Join Properties dialog box. Click View. Access has used the CustomerID field to match customers with orders. In the Record Selector area, note that there are 33 records in your results. |
2. | Click View to return to Design view. Right-click the join line between the two field lists. Select Join Properties to open the Join Properties dialog box. |
3. | Change the selection to 2 (see Figure 8.12). Figure 8.12. In the Join Properties dialog box, you select the join type.You're now using an outer join. Access retrieves all the customer records and only those records from the Orders table in which the CustomerID fields are equal. Don't be deceived by this “only-ness” of the outer join. Only sounds like relatively few records will be matches. In fact, in many companies, most customers are “live” and have orders. So for most of the records, there will be a match, and nearly all your results will include records from both Customers and Orders tables. |
4. | |
5. | Select the OrderID field and click Ascending Sort. The customers without orders are listed first. |
6. | |
7. | Right-click the join line and choose Join Properties. Take a look at option 3 in the dialog box. When would you use this type of outer join? In this case, Access retrieves all the Orders records and only those records in the Customers table in which there are matching customers. But as I've noted, don't be misled by the word only. There should be matches for all the records—what kind of order doesn't have a customer? In the Nifty Lions database, all orders have customers. As you can see, though, this is just the kind of “What's wrong with this picture?” question that an auditor or manager might want to ask. If an order doesn't have an approved customer, that could indicate some type of criminal activity. |
8. |
If you find all this business about innies and outies confusing, Access provides the easy-to-use Find Unmatched Query Wizard as a substitute for many outer-join queries.
Click Queries in the Database window, click New, and choose Find Unmatched Query Wizard in the New Query window. If you understood the previous example, you'll have no trouble dealing with the wizard. Here's an overview of its five screens:
Select the table from which you want to retrieve all the records. In the example where you found customers without orders, that is the Customers table.
Select the table from which you want to find “only” those records with matching data. In the example, that is the Orders table.
Select the field with matching data. Access usually makes it easy for you. In this example, it selects CustomerID for both tables (see Figure 8.13).
Select which fields you want to see in the query. These are fields from the “all” table. In the example, you used the CustLastName and CustFirstName fields.
Name your query and run it.
Just one final word about multitable queries. Although outer joins are indeed useful, there's a reason that inner joins are the default join. Most of the queries you'll do likely will require an inner join. So even if you don't fully understand the difference between inner and outer joins, remember that the type of join you probably want has already been selected for you.
18.116.63.174