Types of Joins

Chapter 9 - Working with Multiple Tables
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

When two tables are joined, the process that takes place is that the first table’s rows and columns are retrieved and then the second table’s rows and columns are retrieved. This means that the linking field appears twice in the resulting data set, once for each table that it appeared in. This is shown in Figure 9.14 with the results of a query joining resort names with finance company names by linking on the ResortCode field.

Figure 9.14. Joining data

When a table join is being performed, the temporary result set is stored in memory. The result set is dependent on the type of join; there are four types:

• Inner join

• Left outer join

• Right outer join

• Full outer join

In many cases, you can change the type of join by clicking the Link Options button to open the Join Type area, shown in Figure 9.15.

Figure 9.15. Join types

Inner Join

Inner joins are performed by default in the Links dialog of the Database Expert. An inner join assumes two tables and a common field between them. The result of an inner join returns all the database records in both tables where the common field is an exact match between the two tables. The resulting virtual table contains all the records in both tables where a match was found between them.

Using a Venn diagram, we show the visual representation of the inner join operation in Figure 9.16. Venn diagrams are used to describe mathematical sets of information that are logical; these diagrams were popularized by John Venn in England during the 1800s. The overlapping area depicts the result set that contains only records that matched from the first table and the second table. As an example, if the Resorts table is inner joined with the ResortFinanceCompany table, linking on the ResortCode field, only those resorts that actually have a finance company will be returned. Refer back to Figure 9.14 for data results for this type of join.

Figure 9.16. Inner join

Left Outer Join

A left outer join returns all the rows from the table on the left plus any rows from the table on the right that match the link field. Using a Venn diagram, we show the visual representation of the left outer join operation in Figure 9.17. The overlapping area depicts the result set that contains only records that matched from the first table and the second table.

Figure 9.17. Left outer join

For an example, if the Resorts table is left outer joined with the ResortFinanceCompany table, the result returns all the rows from the Resorts table plus data for matching Resorts found in the ResortFinanceCompany table. Figure 9.18 shows the data results for a left outer join on the Resorts table and the ResortsFinanceCompany table. Notice that all rows were returned even if no finance company exists because there was an exact match on the ResortCode; nulls are placed in the FinanceCompany field.

Figure 9.18. Left join data

Right Outer Join

A right outer join returns all the rows from the table on the right plus any rows that match in the table on the left based on the link field. Using a Venn diagram, we show the visual representation of a right outer join operation in Figure 9.19.

Figure 9.19. Right outer join

Full Outer Join

A full outer join returns all the rows from the table on the left and all the rows from the table on the right. Using a Venn diagram, we show the visual representation of a full outer join operation in Figure 9.20. Null values are placed in fields where no match existed.

Figure 9.20. Full outer join

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback