Progress
SQL-89
Guide and Reference


Understanding Join Types

Progress/SQL supports two basic types of join: inner and outer. An inner join returns the records selected for the table (or join) on the left side combined with the related records selected from the table on the right. For any records not selected from the right table, the join returns no records from either the left or right sides of the join. Thus, only related records that are selected from both sides are returned for an inner join. Figure 3–1 shows an example of inner joins.

There are three types of outer join:

All outer joins return the records selected for an inner join. In addition, for each set of records selected from the table (or join) on the left side, a left outer join returns unknown values (?) from the table on the right where there is no record selected or otherwise related to the records on the left. That is, records from the left table (or join) are preserved for all unmatched records in the right table. A right outer join is the reverse of a left outer join. That is, for each set of records selected from the table (or join) on the right side, a right outer join returns unknown values from the tables on the left where there is no record selected or otherwise related to the records on the right. That is, records from the right table (or join) are preserved for all unmatched records in the left table. A full outer join returns the results of both a left and right outer join in one results list.

NOTE

Figure 3–2 shows an example of left outer joins. Thus, the results list contains all selected values for the left-most joined table and unknown values for all unmatched records from tables on the right. In a right outer join of the same three tables, the results list would show all selected values for the right-most joined table and unknown values for all unmatched records from tables on the left.

Figure 3–2: Left Outer Joins

Progress SQL provides support for inner, left outer, and right outer joins using two types of join specifications in the FROM clause: implicit and explicit joins. You can specify either implicit joins or explicit joins, but not both in the same FROM clause.


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