Full (Symmetric) Outer Join

DQL Statements › Relationship Clauses › Types of Outer Joins and Disjunctions › Full (Symmetric) Outer Join

Full (Symmetric) Outer Join

To preserve the rows in both tables that cannot be joined with RELATED BY requires an outer join. Each unjoined row is extended with null values where data would have appeared had a match been found. The following query produces a full outer join:

 FIND ALL TABLEA OUTER-JOINED BY ACITY VIA BCITY TO TABLEB

The outer joined output shows an output row that has been extended with nulls (shown as ?) because no match was found.

A#	ACITY	B#	BCITY
A1	London	B1	London
A2	Paris	?	?
A3	?	?	?
A4	NY	B4	NY
A5	Madrid	?	?
?	?	B2	Oslo
?	?	B3	?
?	?	B5	LA

To extend the full outer equijoin to three tables, first join tables A, B, and C using ACITY = BCITY and ACITY = CCITY as in the following query.

When joining three or more tables, it may be best to use a key from a table other than the last named table. In this case, the table name can be repeated before the keyword OUTER-JOINED. For example:

     FIND ALL   TABLEA
      OUTER-JOINED BY  ACITY VIA  BCITY TO  B
TABLEA OUTER-JOINED BY  TABLEA.ACITY  VIA  CCITY  TO  C

This is illustrated by the following table:

A	ACITY	B	BCITY	C	CCITY
A1	London	B1	London	C1	London
A2	Paris	?	?	?	?
A3	?	?	?	?	?
A4	NY	B4	NY	?	?
A5	Madrid	?	?	C4	Madrid
?	?	B2	Oslo	?	?
?	?	B3	?	?	?
?	?	B5	LA	?	?
?	?	?	?	C2	Rome
?	?	?	?	C3	?
?	?	?	?	C5	LA

If the outer join of the tables A, B, and C had been done with ACITY = BCITY and BCITY = CCITY, the resultant table would have been:

A	ACITY	B	BCITY	C	CCITY
A1	London	B1	London	C1	London
A2	Paris	?	?	?	?
A3	?	?	?	?	?
A4	NY	B4	NY	?	?
A5	Madrid	?	?	?	?
?	?	B2	Oslo	?	?
?	?	B3	?	?	?
?	?	B5	LA	C5	LA
?	?	?	?	C2	Rome
?:	?:	?:	?:	C3:	?
?	?	?	?	C4	Madrid