As described in Example 3-9, the join technique we have used so far is theinner join. Note that there is a customer in Spain who has no orders, so that customer’s data did not appear in the Example 3-9 results. If we wish to include all customers in Spain in the results, regardless of whether they have placed orders or not, we need to use an
outer join(also called aninclusivejoin). An outer join returns all rows from one (or both) of the tables, regardless of whether matching rows are found in the joined tables. Any data to be displayed from the table where no matching row is found is set to NULL in the query results. For example, for the customer who has no orders, all the columns from the Orders table would display as NULL in the results. Keep in mind that the returned data rows are still filtered by other search criteria (for example, only customers from Spain; only orders with dates greater than 1/1/98), but whether the filtering occurs before, during, or after the join operation is immaterial, so long as the unwanted rows are eliminated from the query results. Remember, we only de- scribe the result we want, not how to achieve it. There are three types of outer joins, and unfortunately, the industry has settled on potentially confusing names for them:
•
Left Outer Join An outer join for which all rows are returned from the left-hand table in the join, and data from any matching rows found in the right-hand table is also returned.•
Right Outer Join An outer join for which all the rows are returned from the right-hand table in the join, and data from any matching rows found in the left-hand table is also returned.•
Full Outer Join An outer join for which all rows are returned from both tables, regardless of whether matching data is found between them. Microsoft Access does not currently support this type of join.The confusion comes from the use ofleftandrightin the names of the join types. All you have to do is reverse the order of the tables in any existing query, and you are essentially switching it from a left outer join to a right outer join, or vice versa. How- ever, Microsoft Access does not make this distinction, so all its joins are simply calledouter joins.
To change Example 3-9 into an outer join, double-click the thin part of the line between the two tables. This displays the Join Properties dialog box, shown in Figure 3-18. As with the Relationships panel, it can be tricky getting the cursor in exactly the right place on the line, but practice and a bit of patience always prevails. Note that the look of this panel has changed in newer versions of Microsoft Access.
Color profile: Generic CMYK printer profile Composite Default screen
Here are the steps to follow to complete this example:
1. Because we want all the rows from Customers regardless of what is or is not found in the Orders table, option 2 is the one we seek. Click it to select it and then click the OK button.
2. In the criteria for the OrderDate column, addOR IS NULLto the entry. If we fail to do so, we will not see rows in the results for any customers who have no orders. This is because outer joins set unmatched data to NULL, and because null values are not greater than 1/1/1998 (and in fact are not greater than, less than, or equal toanything), the criteria as written in Example 3-9 would eliminate the row we worked so hard to include.
The completed panel and query results are shown in Figure 3-19.
Comparing the query results in Figure 3-19 with the query results in Figure 3-17, note that there is one more row in Figure 3-19. This is customer FISSA, the one who has no orders in the Orders table. For this row, the Order Date, Shipped Date, and Freight columns are null.
An interesting feature of Microsoft Access is that it generates SQL statements for all database queries. And although Microsoft Access SQL does not conform to ANSI standards, the ability to try something on the Query Design View panel and see how that action translates into SQL can be a great learning tool. SQL is carefully presented in the next chapter; so don’t be intimidated by your first look at it here. To see the SQL for the Example 3-10 query, click the small downward-pointing arrow on the toolbar, next to the icon for View, and select the SQL View option from the drop-down list, as shown in the panel at the top of Figure 3-20. Note that newer
76
Databases Demystified
Figure 3-18 Join Properties dialog box
Color profile: Generic CMYK printer profile Composite Default screen
CHAPTER 3
Forms-Based Database Queries
77
Demystified / Databases Demystified / Oppel/ 225364-9 / Chapter 3
versions of Microsoft Access include additional options—PivotTable and PivotChart—in the drop-down menu. The generated SQL, as shown at the bottom of Figure 3-20, is displayed. You can even change the generated SQL and see the changes “reverse engineered” back into the Query Design View panel. This is an amazing product feature.