The final type of join is the outer join, which guarantees that all rows from one or
both of the tables remain in the result set, even when there are no matching rows
in the other table. All the previous joins have been inner joins, meaning that only
rows that match are included. For a cross-join, this does not make a difference
because there are many copies of rows from both tables in the result. However, for
other types of joins, losing rows in one or the other table may not be desirable;
hence the need for the outer join.
Lookups are a good example of an outer (equijoin), because the join asserts that a
foreign key in one table equals a primary key in a reference table. Lookups return
all the rows in the first table, even when there is no matching row.
Outer joins comes in three flavors:
The LEFT OUTER JOIN ensures that all rows from the first table remain in the
The RIGHT OUTER JOIN ensures that all rows from the second table remain.
The FULL OUTER JOIN ensures that all rows from both tables are kept. When
there is no match, then the columns from the “missing” table are all set to NULL
in the result set.
What does this mean? Consider the Orders table, which has some zip codes that
are not in ZipCensus. This could occur for several reasons. ZipCensus contains a
snapshot of zip codes as of the census, and new zip codes might have appeared
since then. Also, the Census Bureau is not interested in all zip codes, so they
exclude some zip codes where no one lives. Or, perhaps the problem might lie in
Orders. There could be mistakes in the ZipCode column. Or, as is the case, the
Orders table might include orders from outside the United States.
Whatever the reason, any query using the inner join eliminates all rows where the
zip code in Orders does not appear in ZipCensus. Losing such rows could be a
problem, which the outer join fixes. The only change to the query is replacing the
word JOIN with the phrase LEFT OUTER JOIN (or equivalently LEFT JOIN):
SELECT zc.stab, COUNT(*) as numrows
FROM Orders o LEFT OUTER JOIN
ON o.ZipCode = zc.zcta5 AND
o.TotalPrice > zc.mediangrossrent
GROUP BY zc.stab
The results from this query are not particularly interesting. The results are the
same as the previous query with one additional large group for NULL. When there is
no matching row in ZipCensus, zc.stab is NULL.
Left outer joins are very practical. When they are chained together, they
essentially say “keep all rows in the first table.” As a general rule, dont mix outer
join types if you can avoid it, because just having LEFT OUTER JOINs and INNER
JOINs is sufficient for most purposes. As an example, if one table contains
information about customers, then subsequent joins could bring in other columns
from other tables, and the LEFT OUTER JOIN ensures that no customers are
In general, you can write queries using just LEFT OUTER JOIN and INNER JOIN.
There is usually no reason to mix LEFT OUTER JOIN and RIGHT OUTER JOIN in
the same query.