Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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.

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

LEFT OUTER JOIN

In addition to the INNER JOIN result, the LEFT OUTER JOIN also includes the rows from table-reference-1 (the table on the left of the JOIN) which do not satisfy the join condition.

Syntax

The syntax variants of the LEFT OUTER JOIN are as follows:







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 SALARIES table.

 SELECT *
 FROM employees
 LEFT OUTER JOIN salaries
     ON employees.id = salaries.id

RIGHT OUTER JOIN

In addition to the INNER JOIN result, the RIGHT OUTER JOIN also includes the rows from table-reference-2 (the table on the right of the JOIN) which do not satisfy the join condition.

Syntax

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







Example

The query below counts the number of realeses for each month during the year 1990, using RIGHT OUTER JOIN to include months without any release.

 select smr.c as month_no, count(i.item_id) as number_of_releases
 from items i
 right outer join system.manyrows smr
     on  extract(month from i.release_date) = smr.c
     and extract(year from i.release_date) = 1990
 where smr.c between 1 and 12
 group by smr.c

FULL OUTER JOIN

A FULL OUTER JOIN combines the effect of both a LEFT OUTER JOIN and a RIGHT OUTER JOIN, i.e. the FULL OUTER JOIN includes the rows from the left table which do not satisfy the join condition, and the rows from the right table which do not satisfy the join condition.

Syntax

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







Example

A full outer join of the SELLERS table with the SUPPLIERS table on the CITY column:

 SELECT sellers.seller_name, suppliers.supplier_name
 FROM sellers
 FULL OUTER JOIN suppliers
     ON sellers.city = suppliers.city

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX