Oracle Outer Joining - Unexpected Result Sets

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.

Thanks,

Andrew. Received on Tue Dec 04 2007 - 12:33:44 CST