|
|
|
|
|
|
The OUTER JOIN
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:
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:
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)
 Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se |
|
|
|
|
|
|
