Guidelines for Using Outer Joins
Introduction
Outer joins, when used properly, provide additional information from a single query that would otherwise require multiple queries and steps to achieve. However, the proper use of outer joins requires training and experience because “common sense” reasoning does not always apply to formulating an outer join query in a manner that is not only syntactically correct, but also returns an answer that is correct for the business question posed.
Guidelines
To ensure that you get the right answers to your business questions using outer joins, refer to these steps.
1 Understand the question you are trying to answer and know the demographics of your tables.
You should have a good idea of what the answer set should look like before you begin (see “Single Table Cardinality Estimate” on page 284 and “Inner Join Cardinality Estimate” on page 284).
2 Write the query, keeping in mind the proper placement of join and search conditions.
|
PLACE these conditions … |
IN this clause … |
|
join |
ON |
|
search condition predicates for inner table |
ON |
|
search condition predicates for outer table |
WHERE |
3 Always EXPLAIN the query before performing it.
Look for the words outer join in the EXPLAIN text. If they are not there, then the Optimizer did not produce an outer join plan.
4 Perform the query and compare the result with your expectations.
|
IF the answer set … |
THEN … |
|
matches your expectations |
it is probably correct. |
|
does not match your expectations |
check the placement of the selection criteria predicates in the ON and WHERE clauses of your outer join. |
For information about the various types of join methods the Optimizer might use in responding to your requests, see Chapter 3, “Join Planning and Optimization,” in SQL Request and Transaction Processing.