Analysis of the Result
Although the request specifies a LEFT OUTER JOIN, the Optimizer treats it as a simple inner join because all the selection criteria are in the WHERE clause, so they are logically applied only after the outer join processing has been completed. Examples 1 and 3 are logically identical, so they produce the same result.
An EXPLAIN shows that the Optimizer recognizes this query to be an inner join and executes it as such (“...joined using a merge join...” rather than “...left outer joined using a merge join...”). Therefore, it executes with the speed of an inner join.
The Optimizer selects the best join algorithm for this inner join, a merge join, and applies the conditions from the WHERE clause. For information about merge join, see SQL Request and Transaction Processing.
Logically, the WHERE clause terms are applied after the terms in the ON clause of
the LEFT OUTER JOIN have been applied, which results in an intermediate step of 18,034
rows. However, the WHERE clause includes B.DATA_YEAR_MONTH=199707, eliminating all rows where the value for B.DATA_YEAR_MONTH is null and returning the 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 shows “...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, is not the correct answer to our business question.