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 join variants (JOIN ON, NATURAL JOIN and JOIN USING) can be applied as a 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

The join variants (JOIN ON, NATURAL JOIN and JOIN USING) can be applied as a 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 is roughly similar to the example in the previous section, but the salaries come to the left in the column list.

 SELECT * FROM salaries RIGHT OUTER JOIN employees
     ON salaries.emp_no = employees.emp_no

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.

Mimer SQL does not support the FULL OUTER JOIN syntax, but a full outer join can be achieved by performing UNION ALL between an INNER JOIN and the "extra" rows from the left and right tables respectively.

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
 

Can be specified as:

 SELECT sellers.seller_name, suppliers.supplier_name
 FROM sellers JOIN suppliers
     ON sellers.city = suppliers.city
 UNION ALL
 SELECT sellers.seller_name, NULL
 FROM sellers
 WHERE NOT EXISTS (SELECT * FROM suppliers WHERE sellers.city = suppliers.city)
 UNION ALL
 SELECT NULL, suppliers.supplier_name
 FROM suppliers
 WHERE NOT EXISTS (SELECT * FROM sellers WHERE sellers.city = suppliers.city)

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se