Re: Oracle Outer Joining - Unexpected Result Sets

On 5 Dez., 18:44, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@xxxxxxx> wrote:
On 4 Dec, 18:33, andre...@xxxxxxxxxxx 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

.

Relevant Pages

  • Re: Sense lining--2nd try
    ... you have to break a clause, keep the subject and predicate together. ... I've never taken a speed reading course. ... I've worked on a lot of PowerPoint presentations, because I was required to do so for a job. ...
    (comp.fonts)
  • Re: Sublists question
    ... Since any of the variables can be s, this clause is certainly true. ... Here is an alternative predicate that does not have that problem. ... What Prolog are you using? ... Anyway, with the new sublist/2 predicate, the query, langford, ...
    (comp.lang.prolog)
  • Re: Search path program
    ... predicate is specified, EQL is used. ... the test-key is eql to any key for that clause", ... specifically mentioned the predicate. ... programmers would be designed like a well-written computer program. ...
    (comp.lang.lisp)
  • case of predicates
    ... Quite often a COND is simply a series of predicates all ... finding the first predicate that is true of the original value. ... (if (eq t (car clause)) ... `((,(car clause),var) ...
    (comp.lang.lisp)
  • Re: Outer joins in MS SQL 2005: why is it this way? better yet, any resources on these nuances?
    ... LEFT OUTER JOIN ... If the predicate tests TRUE for that row, ... supno supno partno qty ...
    (comp.databases.ms-sqlserver)
Loading