Re: Oracle Outer Joining - Unexpected Result Sets

From: Frank van Bortel <>
Date: Tue, 04 Dec 2007 19:55:43 +0100
Message-ID: <fj47re$rjf$> 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:

> 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

Frank van Bortel

Top-posting is one way to shut me up...
Received on Tue Dec 04 2007 - 12:55:43 CST

Original text of this message