FULL [OUTER] JOIN

In contrast to inner joins, outer joins can contain some or all nonmatching rows from both joined tables. Missing values in a row of the result table are filled with nulls. The following sample query creates a full outer join between the PART and PARTSUPP tables:

SELECT part.pno, pname, qty
  FROM part FULL OUTER JOIN partsupp
  ON part.pno = partsupp.pno

The results of this full outer join (the keyword OUTER is optional) contain all matched as well as unmatched rows from either table. For example, if the PART table contained a nonmatching record for a RIVET with a PNO column value of P8, this RIVET information would be part of the result, even though there is no matching record in the PARTSUPP table (with a PNO column value of P8). Likewise, if the PARTSUPP table contained a nonmatching record with a PNO column value of P7, this record information would also appear in the result even though there is no matching P7 PNO column value in the PART table.

Using the FULL [OUTER] JOIN syntax includes nonmatched rows from both joined tables. Full outer joins can only use the equal (=) comparison operator in the join condition (left and right outer joins can use all the comparison operators).