Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Outer Joining - Unexpected Result Sets

Re: Oracle Outer Joining - Unexpected Result Sets

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 7 Dec 2007 04:01:59 -0800 (PST)
Message-ID: <4908778c-e403-4fa0-9ebe-8ef1f49464f3@t47g2000hsc.googlegroups.com>


On 5 Dez., 18:44, David Portas
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> 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.

Andrew, my guess at what happens here: when the (+) is used on the non join conditions it is considered part of the join while when (+) is removed the condition is part of the where clause. When it's part of the join clause then it filters rows from the joined table /before/ the join takes place, otherwise afterwards. So you potentially get less rows when you filter later (afterwards, in where clause).

To see an example of this working you can look at a recent thread here "Conditional Join". There the same happened but with ANSI join. The crucial bit to remember is that it makes a difference where you put the condition when doing an outer join.

Kind regards

robert Received on Fri Dec 07 2007 - 06:01:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US