Re: Oracle Outer Joining - Unexpected Result Sets

andrewah_at_hotmail.com wrote:
> Hi there.
>
> I was wondering if anyone could correct my knowledge of outer joins in
> Oracle.
>
> I have been presented with a script which is used to produce a .csv
> extract for business users on a nightly basis.
>
> I have been asked to convert it into a report which can be run by
> these users via the main back-office application as they see fit.
>
> The main driving query of the script that I have been presented with,
> has a predicate which features lines similar to these:
>
> ... Table_1.Col_1 (+) = Table_2.Col_1
> AND Table_1.Col_2 (+) = CONSTANT_1
> AND Table_1.Col_3 (+) = CONSTANT_2

Outer join - some of the rows may not satisfy the join condition, but are included in the result set; see: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054062

> Now, to my mind because the 2nd and 3rd lines are not part of the join
> condition between Table_1 & Table_2, the above query predicate should
> be equivalent to:
>
> ... Table_1.Col_1 (+) = Table_2.Col_1
> AND Table_1.Col_2 = CONSTANT_1
> AND Table_1.Col_3 = CONSTANT_2

It is not.
>
> - i.e. without the 2 (+)s.
>
> However, after making this change, the result set changed in 2 ways:
>

Yes, that could be the result. It is not a given fact, though.

> 1. The number of rows returned dropped from 971 to 970.
> 2. This query invokes several aggregate functions, and two columns in
> the result set are if the type: COUNT(DISTINCT Table_1.Col_X). The
> numbers returned in this column by Oracle are different depending on
> the predicate.
>
> Incidentally, I have tried to rewrite this using ANSI joins, and the
> results are consistent with the result-set returned after modifying
> the predicate of the query (Removing the (+)s).
>
> To my mind this is worrying as I do not believe a query with the
> predicate equivalent to the one defined first in this post could be
> written using ANSI standard joins - given that they are not equivalent
> of course.
>

I think, after reading the reference I gave, your questions will be explained

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...