You can specify join conditions (table relations) using the OF option or WHERE option of the Record phrase that specifies the join. The OF option specifies an implicit join condition based on one or more common field names in the specified tables. The common field names must participate in a unique index for at least one of the tables. The WHERE option can specify an explicit join based on any field relations you choose, and you can use this option further to specify selection criteria for each table in the join. For an inner join, if you do not use either option, the AVM returns a join of all records in the specified tables. For a left outer join, you must relate tables and select records using the OF option, the WHERE option, or both options.
Note: Work tables and temporary tables can also participate in joins. However, work tables do not have indexes. So, if you specify join conditions using the OF option with a work table, the other table in the join must be a database or temporary table with a unique index for the fields in common. For more information on work tables and temporary tables, see OpenEdge Getting Started: ABL Essentials.
The following code fragment generates the left outer joins shown in the following figure:
DEFINE QUERY q1 FOR Table1, Table2, Table3.
OPEN QUERY q1 FOR EACH Table1, EACH Table2 OUTER-JOIN WHERE C11 = C21,
EACH Table3 OUTER-JOIN WHERE C22 = C31.
GET FIRST q1.
DO WHILE AVAILABLE(Table1):
DISPLAY C11 C12 C21 C22 C31 C32 WITH TITLE "Join123".
GET NEXT q1.
END.
Note that the Record phrase for the right-hand table of each join specifies the OUTER–JOIN option. As the following figure shows, the primary benefit of a left outer join is that it returns every record on the left-hand side, whether or not related data exists on the right.
