Using Outer Joins

Go Up to Joining Tables (Embedded SQL Guide)

Outer joins produce a results table that contains columns from every row in one table, and a subset of rows from another table. Actually, one type of outer join returns all rows from each table, but this type of join is used less frequently than other types. Outer join syntax is very similar to that of inner joins:

SELECT col [, col ...] | *
FROM <tablerefleft> {LEFT | RIGHT | FULL} [OUTER] JOIN
<tablerefright> [ON <searchcondition>]
[WHERE <searchcondition>];

Outer join syntax requires that you specify the type of join to perform. There are three possibilities:

• A left outer join retrieves all rows from the left table in a join, and retrieves any rows from the right table that match the search condition specified in the ON clause.
• A right outer join retrieves all rows from the right table in a join, and retrieves any rows from the left table that match the search condition specified in the ON clause.
• A full outer join retrieves all rows from both the left and right tables in a join regardless of the search condition specified in the ON clause.

Outer joins are useful for comparing a subset of data to the background of all data from which it is retrieved. For example, when listing those countries which contain the sources of rivers, it may be interesting to see those countries which are not the sources of rivers as well.

Topics

• Using a Left Outer Join
• Using a Right Outer Join
• Using a Full Outer Join
• Sort/Merge Optimization for Outer Joins