Page 6 of 13PREVNEXT

Queries VII: Query multiple sources for data

Outer joins return all data from the base table, and only matching data from the secondary table

Outer joins find matching data and more.

The other common type of join is called an outer join. Outer joins are where the "left" and "right" sides of a join come into play. In a left outer join, the query returns all the data from the table on the left side of the join, and matching data from the table on the right. The table on the left is called the base table, and the table on the right is the secondary table. The picture shows the results of a typical left outer join.

In a right outer join, the opposite is true. The base table is on the right and the secondary table is on the left. Also, remember that the location of a table in a join may not correspond to that table's location on your screen. For example, in the query designer, a table on the left side of a join may appear on the right side of your screen. If you need to know which side of a join a table is on, you use the Join Properties dialog box, and we'll show you how later.

Outer joins can be a powerful tool. For example, if you use Products as a base table and Orders as a secondary table, you can easily see which products have and haven't sold. You create left and right outer joins by editing a join, and you'll see how later in this course, and in the practice.

Note     You can also use two more types of joins — cross joins and unequal joins — but you won't need to very often. For more information about them, see the links in the Quick Reference Card.

Page 6 of 13PREVNEXT