Previous Topic Next topic Print topic


LEFT [OUTER] JOIN

Left outer joins (the keyword OUTER is again optional) include nonmatching rows only from the table named before the LEFT [OUTER] JOIN clause. Missing values in a row are filled with nulls. The following sample query creates a left outer join between the PART and PARTSUPP tables:

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

The results of this query contains all matched rows, plus unmatched rows from the PART table (or, the table on the left hand side of the LEFT OUTER JOIN clause). For example, if the PART table contained a nonmatching record for a WASHER with a PNO column value of P9, this WASHER information would be part of the result, even though there is no matching record in the PARTSUPP table with a PNO column value of P9. If, however, the PARTSUPP table also contained an unmatched record, this record information would not appear in the result rows. Using the LEFT [OUTER] JOIN syntax includes nonmatched rows from only the joined table on the left of the LEFT OUTER JOIN clause.

Previous Topic Next topic Print topic