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.

Syntax

The syntax for the variants of the LEFT OUTER JOIN is 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 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.

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. Also, the natural clause in the previous example omits the common columns.

 SELECT * FROM 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.

Example

A full outer join of the SELLERS table with the SUPPLIERS table on the CITY column is specified as follows:

 SELECT city FROM seller
     WHERE NOT EXISTS (SELECT * FROM supplier WHERE supplier.city = seller.city)
 UNION ALL
 SELECT seller.city FROM seller INNER JOIN supplier
     ON seller.city = supplier.city
 UNION ALL
 SELECT city FROM supplier
     WHERE NOT EXISTS (SELECT * FROM seller WHERE supplier.city = seller.city)

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