Analysis of the Result
The query specifies a left outer join without qualifying the results table with a WHERE clause. The Optimizer constructs a plan for a left outer join as specified by the query. The result is a join of every row in the outer table (17,713,502 rows) with the qualified rows of the inner table, those service types of either ABC or XYZ in district K in the month of July, 1997 (13,010 rows).
The cardinality of this result, 17,713,502 rows, is three orders of magnitude larger than the correct answer to the business question.
Without a WHERE clause to qualify the result, an outer join result always contains at least 1 row for every row in the outer table.
Qualifying predicates placed in the ON clause do not eliminate rows from the result. Rather, they are treated as rows that are not matched with the inner table irrespective of whether join terms that reference both the inner and outer tables evaluate to TRUE. In other words, the selection criteria of an ON clause only define the rows to which nulls are to be appended for nonmatching rows. They do not restrict the result to rows matching the conditions they specify.
The Optimizer does qualifies the result to a degree, returning only those rows for
which the predicate b.data_year_month = 199707 evaluates to TRUE.
The Optimizer selects the best join algorithm for this left outer join (a merge join) and applies the conditions from the ON clause.
The result of the query, 17,713,502 rows, which is not the correct answer to our business question.