Outer Joins

Suppose you wanted to display information about all suppliers and the parts they supply. Joining the SUPPLIER and PARTSUPP tables will retrieve this information for every supplier that has provided parts. But you would not see any information for suppliers that have not yet supplied parts. You would have no indication that such a supplier even existed.

An outer join is a special kind of join that solves this problem by finding all of the matches between the joined columns, just like a regular join. However, unlike a regular join, those records in one table that have no match at all in the second table are identified.

An outer join is designated by a plus sign in parentheses on the side of the child table. The parent table is the one for which you want rows even if there are no matching child records in the other table.

To create an outer join condition:

1. Define a regular equijoin using the auto join facilities, or by dragging from one column to another.
2. Double click the = operator in the small box on the join line.

   SQLWizard displays the Join Options dialog box.

3. Select a comparison operator from the drop-down list in the center of the dialog box. Make sure the operator includes the (+) on the child side - that is, on the side where you expect to see null values.
4. Click OK. The new comparison operator appears in the join condition.
5. When you run the command you should see some rows with values for the parent table fields, but only null values for the child rows.
   Note:

   Outer joins are not supported against all database systems. For example, outer joins are not supported against XDB Server in DB2 mode, or when running against DB2.