SAND CDBMS SQL Reference Guide
Joins

OUTER JOINS

In contrast to inner joins, outer joins can contain non-matching rows from both joined tables. Missing values in a row of the result table are filled with nulls. The OUTER JOIN syntax requires that all of the join conditions appear in the ON clause. All predicates that appear in a WHERE clause are evaluated as a filter after the join, even if the predicates could have been added as another join condition.

FULL [OUTER] JOIN

The result of the full outer join contains all matched as well as unmatched rows from both tables.

The following sample query creates a full outer join between the Part and Partsupp tables:

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

If the Part table contained a non-matching 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.

LEFT [OUTER] JOIN

Left outer joins include non-matching rows only from the table named before (that is, to the left of) 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 result of this query contains all matched rows, plus unmatched rows from the Part table (that is, the table on the left-hand side of the LEFT OUTER JOIN clause). For example, if the Part table contains a non-matching record for a "WASHER" with a pno column value of "P9", this "WASHER" information will 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.

Note that the OUTER keyword is optional.

RIGHT [OUTER] JOIN

Right outer joins  include non-matching rows only from the table named after (that is, to the right of) the RIGHT OUTER JOIN clause. Missing values in a row are filled with nulls.

The following sample query creates a right outer join between the Part and Partsupp tables:

   SELECT part.pno, pname, qty
     FROM part RIGHT JOIN partsupp
     ON part.pno = partsupp.pno;

The result of this query contains all matched rows, plus unmatched rows from the Partsupp table (that is, the table on the right-hand side of the RIGHT JOIN clause). For example, if the Partsupp table contained a non-matching record for a pno column value of "P9", this information would be part of the result. If, however, the Part table also contained an unmatched record, this record information would not appear in the result rows.