Outer Join

An outer join creates two distinct kinds of query output one after the other -- all in the same result (not available in DB2 mode). Records satisfying the join condition appear first in the result, followed immediately by records that do not satisfy the join condition.

For example, an ordinary join between the SUPPLIER and PARTSUPP table could be performed using the following query:

SELECT supplier.sno, sname, pno, qty
  FROM supplier, partsupp
  WHERE supplier.sno = partsupp.sno

However, the output result would not include any information on supplier "S5", since this is the one supplier that does not supply any parts. (See the sample tables in XDB Server TUTORIAL Location.)

By performing an outer join, it is possible to retrieve the information on supplier "S5" from the SUPPLIER table (specifically the SNO and SNAME column values), even though the join condition of the WHERE clause (supplier.sno = partsupp.sno) is not being met for this row of joined output.

SELECT supplier.sno, sname, pno, qty
  FROM supplier, partsupp
  WHERE supplier.sno = partsupp.sno(+)

The sample query above illustrates the syntax for an outer join. Appending a plus sign within parentheses (+) to the PARTSUPP table name in the join condition yields the following results:

Even though the PARTSUPP table contains no rows with a matching "S5" value, the outer join causes the "S5" row of the SUPPLIER table to be output anyway (immediately after all the rows matching the join condition). Since there are no matching PNO or QTY column values from the PARTSUPP table for that joined row of output, the outer join inserts null values in the PNO and QTY columns for this result row.

The outer join conditions must be columns and not expressions. There is a maximum of five outer join conditions per table. Only one master table can be defined.