Analysis of the Result
This example is similar to Example 1. The Optimizer treats this query as an inner join even though the request explicitly specified an outer join. The WHERE clause on the inner table logically changes this query from an outer join to an inner join.
As in previous examples, the WHERE clause is logically applied after the outer join processing completes, removing all rows that were nulled in the process: nonmatching rows between the left and right tables. The Optimizer knows to execute this as an inner join to improve the performance of the query.
The EXPLAIN text matches the EXPLAIN text for Example 1. As expected, the answer set also matches.
The Optimizer selects the best join algorithm for this inner join (a merge join) and applies the conditions from the WHERE clause.
Logically, the WHERE clause term is applied after the terms in the ON clause of the
LEFT OUTER JOIN have been applied. That would result in an intermediate step of 18,034
rows. However, the term b.data_year_month=199707 from the WHERE clause would then be applied, eliminating all rows where the value
for b.data_year_month is null, and returning the final, incorrect, result of 13,010 rows.
The Optimizer recognizes that the WHERE clause references an inner table that does not evaluate to TRUE for nulls and is therefore equivalent to an inner join. As a result, it generates a plan for a simple inner join, ignoring the request for a left outer join.
The EXPLAIN text says “...SAMPDB.CUSTOMER and SAMPDB2.REVENUE are joined using a merge join...” Had the plan used an outer join, the EXPLAIN text would have said “...SAMPDB.CUSTOMER and SAMPDB2.REVENUE are left outer joined using a merge join...”
The result of the query, 13,010 rows, which is not the correct answer to our business question.