This case study was originally developed by Rolf Hanusa and is used with his permission. The text has been edited.
The outer join is a powerful tool, and its output can be as difficult to interpret as it is to produce. A lack of understanding can produce unexpected, costly, and undesired results. For example, you might erroneously mail promotional fliers to 17 million customers instead of the 17,000 customers that you intended to target.
This case study shows some common pitfalls of the outer join, including how a poorly worded outer join request can result in a simple inner join and how an improperly constructed outer join can return millions of rows that do not answer the business question you are trying to ask.
The study also provides guidelines to help you get a feeling for whether you are answering the business question you think you are asking with your outer joins. The study helps you to understand the complexity of outer joins by using realistic examples and explanations.
As this case study shows, many results are possible, and the correct solution is not necessarily intuitive, especially in a more complex query.
This case study contains: