Progress
Language Tutorial
for Windows


Inner and Outer Table Joins

You have learned that you can refer to multiple tables in a single statement using a join. A join is a binary operation that selects and combines the records from multiple tables so that each result in the results list contains a single record from each table. A join combines the records from one table with those from another table or a previous join.

When you specify a join, you also specify conditions that determine how one table relates to another. These are called join conditions. Join conditions control which records selected from one table join with the records in the other table.

Progress supports two types of joins:

Figure 9–4 illustrates the results of an inner join. Note that the only values included from Table1 are the values that match the join conditions. Also, the value where Field1 = 2 appears twice in the join because there are two instances where Field3 = 2 in Table2. For any values in Field1 that do not match a value in Field3, the entire Table1 record is excluded from the join. Likewise, for any values in Field3 that do not match any value in Field1, the entire Table2 record is excluded from the join.

Figure 9–4: Inner Join

Figure 9–5 illustrates the results of a left outer join. Note that all values in Table1 are included in the join, even where they do not have a corresponding value in Table2. The unknown value is used in Field3 and Field4 where there is no value in Field3 corresponding to the value of Field1. Thus, the values of Field3 that do not match any values in Field1 are still excluded from the join.

Figure 9–5: Left Outer Join

Practice Problems

Using a copy of procedure lt-09-04.p as a starting point, figure out the correct syntax for these queries:

Problem 9-2: lt-09-s2.p

Display the customer name and the sales representative for each customer. What kind of relationship is this?

Problem 9-3: lt-09-s3.p

For each customer, list all the product names currently on order. What type of relationship is this?


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095