Outer, Self, and Theta Joins

The preceding sections of this chapter described the inner join, which is the most common type of join in database applications. Jet also lets you create three other joins: outer, self, and theta. The following sections describe these three less-common types of joins, which also apply to SQL Server views, functions, and stored procedures.

Creating Outer Joins

Outer joins let you display the fields of all records in a table participating in a query, regardless of whether corresponding records exist in the joined table. Jet lets you choose between left and right outer joins.

A left outer join query displays all records in the first table your specify, regardless of whether matching records exist in second table. For example Table1 LEFT JOIN Table2 displays all records in Table 2. Conversely, a right outer join query displays all records in the second table, regardless of a record's existence in the first table. Records in the second table without corresponding records in the first table usually, but not necessarily, are orphan records; these kinds of records can have a many-to-one relationship to another table.

To practice creating a left outer join to detect whether records are missing for an employee in the HRActions table, follow these steps:

1. Open the Employees table and add a record for a new (bogus) employee. You need only add values for the LastName and FirstName fields.

2. Open a new query and add the Employees and HRActions tables.

3. Drag the EmployeeID field symbol to the EmployeeID field of HRActions to create an inner join between these fields if Access doesn't create the join automatically.

4. Select and drag the LastName and FirstName fields of the Employees table to columns 1 and 2 of the Query Design grid. Select and drag the ActionType and ScheduledDate fields of the HRActions table to columns 3 and 4.

5. Click the line joining EmployeeID with EmployeeID to select it, as shown in Figure 11.39. The thickness of the center part of the line increases to indicate the selection. (In Figure 11.39, the two Field List boxes are separated so that the thin section of the join line is apparent.)

   Figure 11.39. Double-clicking the thin region of the join line opens the Join Properties dialog.

   

6. Double-click the thin section of the join line to open the Join Properties dialog. (Double-clicking either of the line's thick sections displays the Query Properties window.) Type 1 is a conventional inner join, type 2 is a left join, and type 3 is a right join.

7. Select a type 2 join a left join by selecting option 2 (see Figure 11.40). Click OK to close the dialog.

   Figure 11.40. Select the option for a type 2 join, which includes all records in the left table and only those records of the right table where the two column values match.

   

   Note

   Access adds an arrowhead to the line that joins EmployeeID and EmployeeID. The direction of the arrow, left to right, indicates that you've created a left join between the tables, assuming that you haven't moved the field lists from their original position in the table.

8. Click the Run button to display the result of the left join query. In Figure 11.41, the employee you added without a record in the HRActions table appears in the result table's last active row. (Your query result set might differ, depending on the number of entries that you made when creating the HRActions table.)

   Figure 11.41. A record for EmployeeID 10 with no HRActions record(s) appears in this left outer join.

   

9. Close, but don't save, the query, and then delete the bogus record in the Employees table.

If you could add an HR department action for a nonexistent EmployeeID (referential integrity rules for HRActions table prevent you from doing so), a right join would show the invalid entry with blank employee name fields.

Creating Self-Joins

Self-joins relate values in a single table. Creating a self-join requires that you add a copy of the table to the query and then add a join between the related fields. An example of self-join use is to determine whether supervisors have approved HRActions that they initiated, which is prohibited by the imaginary personnel manual for Northwind Traders.

To create this kind of self-join for the HRActions table, follow these steps:

1. Open a new query and add the HRActions table.

2. Add another copy of the HRActions table to the query by clicking the Add button again. Access names the copy HRActions_1. Close the Show Tables dialog.

3. Drag the original table's InitiatedBy field to the copied table's ApprovedBy field. The join appears as shown in the upper pane of Figure 11.42.

   Figure 11.42. A self-join returns rows for which values of two fields in the same table are equal.

   

4. Drag the EmployeeID and InitiatedBy fields of the original table, and the ApprovedBy and ActionType fields of the copy of the HRActions table, to the Field row of columns 1 4, respectively, of the Query Design grid.

5. With self-joins, you must specify that only unique values are included. (If you don't specify unique values, the query returns every row.) Click the Properties toolbar button or double-click an empty area in the Query Design window's upper pane, set the value of the Query Properties window's Unique Values property to Yes, and close the Query Properties window.

6. Click the Run button to display the records in which the same employee initiated and approved an HR department action, as shown in Figure 11.43. In this case, EmployeeID 1 (Nancy Davolio) was the first employee; EmployeeID 2 (Andrew Fuller) is a vice-president and can override personnel policy. (Your results might differ, depending on the entries you made in the HRActions table.)

   Figure 11.43. This datasheet displays the result set of the design of Figure 11.42. If you don't set the Unique Values property to Yes, the result set has 27 rows.

   

Creating Not-Equal (Theta) Joins with Criteria

Most joins are based on fields with equal values, but sometimes you need to create a join on unequal fields. Joins that you create graphically in Access are restricted to conventional equi-joins and outer joins. You can create the equivalent of a not-equal theta join by applying a criterion to one of the two fields you want to test for not-equal values.

Finding customers that have different billing and shipping addresses, as mentioned previously, is an example in which a not-equal theta join is useful. To create such a join, follow these steps:

1. Create a new query and add the Customers and Orders tables.

2. Select the Customers table's CompanyName and Address fields and the Orders table's ShipAddress field. Drag them to the Query Design grid's first three columns.

3. Type <>Customers.Address in the Criteria row of the ShipAddress column. The Query Design window appears as shown in Figure 11.44.

   Figure 11.44. Not-equal joins require a not-equal (<>) WHERE clause criterion to establish the join.

   

   Note

   Typing <>Orders.ShipAddress in the Address column gives the same result as <>Customers.Address.

4. Click the Properties toolbar button or double-click an empty area in the Query Design window's upper pane to open the Query Properties window and set the value of the Unique Values property to Yes. Otherwise, the query returns a record for every order with a different ship address.

5. Run the query. Only the records for customers that placed orders with different billing and shipping addresses appear, as shown in Figure 11.45.

   Figure 11.45. The result set of the not-equal join displays customers with different shipping and billing addresses. If you don't set the Unique Values property to Yes, the query returns 51 rows.

   

6. Click the Close Window button and save your query if you want.

Note

As mentioned in the earlier "Creating Multicolumn Inner Joins and Selecting Unique Values" section, you can create a theta-join by changing the operator of a equi-join criterion from = to <>. If you do this, you receive an error message every time you open the query in Design view.