Outer, Self, and Theta Joins

The preceding sections of this chapter described the equi-join or, in the parlance of SQL-92, an inner join. Inner joins are the most common type of join in database applications. Access also lets you create three other joins: outer, self, and theta. The following sections describe these three less common types of joins.

Creating Outer Joins

Outer joins allow you to display the fields of all records in a table participating in a query, regardless of whether corresponding records exist in the joined table. With Access, you can choose between left and right outer joins.

When diagramming database structures (a subject of Chapter 22, "Exploring Relational Database Design and Implementation"), you traditionally would draw the primary one table to the left of the secondary many table. A left outer join (LEFT JOIN or *= in SQL) query in Access, therefore, displays all of the records in the table with the unique primary key, regardless of whether matching records exist in the many table. Conversely, a right outer join ( RIGHT JOIN or =*) query displays all of the records in the many table, regardless of a record's existence in the primary table. Records in the many table without corresponding records in the one table usually, but not necessarily, are orphan records; these kinds of records may 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 Personnel Actions table, follow these steps:

1.	Open a new query and add the Employees and Personnel Actions tables.
2.	Drag the EmployeeID field symbol to the paID field of Personnel Actions to create an equi-join between these fields if Access didn't create the join. (Access automatically creates the join if you established a relationship between these two fields when you created the Personnel Actions table in Chapter 4, "Working with Access Databases and Tables.")
3.	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 paType and paScheduledDate fields of the Personnel Actions table to columns 3 and 4.
4.	Click the line joining EmployeeID with paID to select it, as shown in Figure 10.29. The thickness of the center part of the line increases to indicate the selection. (In Figure 10.29, the two Field List boxes are separated so that the thin section of the join line is apparent.) Figure 10.29. Selecting a join to change its property from an inner to a left or right outer join.
5.	Choose View, Join Properties. (The Join Properties command is active only after you select an individual join with a mouse click.) You also can double-click the thin section of the join line. (Double-clicking either of the line's thick sections displays the Query Properties window.) The Join Properties dialog in Figure 10.30 appears. Type 1 is a conventional inner join, Type 2 is a left join, and Type 3 is a right join. Figure 10.30. The Join Properties dialog for choosing inner, left, or right joins.
6.	Select a Type 2 join—a left join—by choosing 2. Click OK to close the dialog. Notice that Access adds an arrowhead to the line that joins EmployeeID and paID. The direction of the arrow, left to right, indicates that you have created a left join between the tables.
7.	Click the Run button of the toolbar to display the result of the left join query. In Figure 10.31, three employees without a record in the Personnel Actions table appear in the result table's last rows. Your query result set may differ, depending on the number of entries that you made when creating the Personnel Actions table. (If all employees show a personnel action, open the Personnel Actions table, delete the entries for a few employees, and then rerun the query.)
8.	Close, but don't save, the query.

If you could add a personnel action for a nonexistent EmployeeID (the validation rule that you added in Chapter 9, "Understanding Operators and Expressions in Access," prevents 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 to the query a copy of the table and then add a join between the related fields. An example of self-join use is to determine whether supervisors have approved personnel actions that they initiated, which is prohibited by the imaginary personnel manual for Northwind Traders.

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

1.	Open a new query and add the Personnel Actions table.
2.	Add to the query another copy of the Personnel Actions table by clicking the Add button again. Access names the copy Personnel Actions_1. Close the Show Tables dialog.
3.	Drag the original table's paInitiatedBy field to the copied table's paApprovedBy field. The join appears as shown in the upper pane of Figure 10.32.
4.	Drag the paID and paInitiatedBy fields of the original table, and the paApprovedBy and paType fields of the copy of the Personnel Actions 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. Click the Properties button on the toolbar or double-click an empty area in the Query Design window's upper pane, and set the value of the Query Properties window's UniqueValues property to Yes. Click the Properties button again to close the Query Properties window. Figure 10.32. Designing the query for a self-join on the Personnel Actions table.
6.	Click the Run button of the toolbar to display the records in which the same employee initiated and approved a personnel action, as shown in Figure 10.33. In this case, EmployeeID 2 (Mr. Fuller) is a vice president and can override personnel policy. (Your results may differ, depending on the number of entries that you made in the Personnel Actions table.)

In this example, you can add the Employees table to the query in order to display the employee name. Adding the Employees table creates an additional join between the original paID field of the Personnel Actions table and the EmployeeID field of the Employees table. You then must drag the LastName field to the Query Design grid's fifth column. Because this join includes a primary-key field, EmployeeID, the default DISTINCT process yields unique values. To verify that the values are unique, click the Properties button of the toolbar, or double-click an empty area in the Query Design window's upper pane. Set the value of the UniqueValues property to Yes and then rerun the query.

▸▸ See Maintaining Referential Integrity

Full-fledged relational database applications seldom use self-joins because validation criteria and enforcement of referential integrity can (and should) eliminate the types of problems that self-joins can detect.

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 with graphical QBE 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 the equivalent of this 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. (Access automatically adds square brackets surrounding table and field names, regardless of whether the names include spaces or other punctuation.) The Query Design window appears as shown in Figure 10.34. This criterion adds the WHERE Orders. ShipAddress <>[Customers].[Address] clause to the SQL SELECT statement shown in Figure 10.35. Typing <> Orders.ShipAddress in the Address column gives an equivalent result. This criterion adds a WHERE Customers.Address <> Orders.[ShipAddress] clause to the SQL SELECT statement. Figure 10.34. Designing the query for a not-equal theta join.
4.	Click the toolbar's Properties 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. Figure 10.35. The SQL statement for a not-equal theta join.
5.	Run the query. Only the records for customers that placed orders with different billing and shipping addresses appear, as shown in Figure 10.36.
6.	Click the Close Window button and save your query, if desired.