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:
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.
The syntax for the variants of the RIGHT OUTER JOIN is as follows:
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.
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