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:
- Inner join — Supported in all statements capable of reading multiple tables, including the FOR, DO, REPEAT, and OPEN QUERY statements. An inner join returns the records selected for the table (or join) on the left side of the join with the related records selected from the table on the right side of the join. If a value in the left table does not have a corresponding value in the right table, Progress does not return that value or a result for the results list. This is an example of an inner join with the FOR statemen:.
In this join, Table1 is on the left side of the join and Table2 is on the right, and Field1 = Field3 is a join condition. For any records not selected from Table2, the join returns no records from either the Table1 or Table2. Only records that are selected from both tables are returned for an inner join.
- Left outer join — Supported only in the OPEN QUERY statement. An outer join returns the same set of records selected for an inner join. However, outer joins also return all the records from the left table. When a value from the left table does not have a corresponding value in the right table, Progress returns the left-table value with an unknown value (?) from the right table. This is an example of a left outer join with the OPEN QUERY statement:
In this join, Table1 is on the left side and Table2 is on the right, and Field1 = Field3 is a join condition.
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
![]()
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |