http://developer.mimer.com

OUTER JOINs

---

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.

LEFT OUTER JOIN Syntax

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

LEFT OUTER JOIN Example

The query below will return a result set containing all employees at least once even though they might not have an entry in the salary table.

 EMPLOYEES NATURAL LEFT OUTER JOIN salaries

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.

RIGHT OUTER JOIN Syntax

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

RIGHT OUTER JOIN Example

The query below is roughly similar to the example in the previous section, but the salaries come to the left in the column list. Also, the natural clause in the previous example omits the common columns.

 SALARIES RIGHT OUTER JOIN EMPLOYEES ON salaries.emp_no=employees.emp_no

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)

Standard Compliance

This section summarizes standard compliance concerning JOIN.

Standard	Compliance	Comments
X/Open-95 SQL-92	YES	Fully compliant.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se