Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


The OUTER JOIN


A table resulting from an inner join, as just described, will only contain those rows that satisfy the applicable join condition. This means that a row in either table which does not match a row in the other table will be excluded from the result.

In an OUTER JOIN, however, a row that does not match a row in the other table is also included in the result table. Such a row appears once in the result and the columns that would normally contain information from the other table will contain the NULL value.

LEFT OUTER JOIN

The inner join variants (NATURAL JOIN, JOIN USING and JOIN ON) can be applied as a LEFT OUTER JOIN. The LEFT OUTER JOIN includes the rows from table-reference-1 (the table on the left of the JOIN) which do not satisfy the join condition.

The syntax for the variants of the LEFT OUTER JOIN is as follows:

RIGHT OUTER JOIN

The inner join variants (NATURAL JOIN, JOIN USING and JOIN ON) can be applied as a RIGHT OUTER JOIN.

The RIGHT OUTER JOIN includes the rows from table-reference-2 (the table on the right of the JOIN) which do not satisfy the join condition.

The syntax for the variants of the RIGHT OUTER JOIN is as follows:

FULL OUTER JOIN

A full outer join can be achieved by performing a UNION between a LEFT OUTER JOIN and RIGHT OUTER JOIN.

For example, a full outer join of the SUPPLIERS table with the PARTS table on the CITY column is specified as follows:

 SUPPLIERS LEFT OUTER JOIN PARTS USING (CITY)
 UNION
 SUPPLIERS RIGHT OUTER JOIN PARTS USING (CITY)


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Reference Manual TOC PREV NEXT INDEX