On 4 Dec, 18:33, andre..._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
>
> 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
>
> - i.e. without the 2 (+)s.
>
> However, after making this change, the result set changed in 2 ways:
>
> 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.
>
> If anyone can suggest a reason for this happening & enhance my
> knowledge then it would be appreciated.
>
> We are running Oracle 10G2.02.
>
In that case you have the standard SQL outer join syntax ({LEFT |
RIGHT | FULL} [OUTER] JOIN) available to you and no need to rely on
the proprietary Oracle language (+). The ANSI/ISO/IEC standard syntax
is more powerful and arguably much easier to understand.
--
David Portas
Received on Wed Dec 05 2007 - 11:44:27 CST
