Orange Coast Database Associates

Left Outer Join with Partial Match
There are some subleties to outer joins involving partial matches. We have looked at joins where we want to show all the rows from the one side of a relationship. Or conversely the rows from the one side that have no matches on the many side. But what if we want to see all the rows from the one side and only a subset from the many side? For eaxmple show all my customers along with their orders for last year. Our first pass at this might be something like the query below.

But this will not work since the filter for 1999 orders effectively masks out the effect of the outer join. We don't want to filter out customers, just orders. The missing values should be replaced by nulls as is normal for outer joins.

Solution
The solution to the above is to prefilter the orders and then join the results of that to the customers using an outer join. We can prefilter the orders in a derived table or inline view using the following syntax.

Syntax

select columns
from table1
left join
(select statement) derived table alias
on table1 key = derived table key

Sample Code
Here is the actual code using the bookstore database (download from Box.net).

Results

Here is an extract of the results of running the above query, Emily Jones and Mary Collins did not place any orders in 1999 but were still listed.

Follow Up
We will expand on this concept in future additions to this post. Hopefully this post will have shown you how to work with outer joins that partially match. It also provided a look at inline views or derived tables which we will cover in more detail in other posts.