Re: Conditional Join

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 26 Nov 2007 21:23:38 +0100
Message-ID: <5r0oacF11qv5sU1@mid.individual.net>

On 26.11.2007 18:16, ErikYkema wrote:
> On Nov 25, 5:05 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:

>> On 22.11.2007 10:22, Peter wrote:
>>
>>> I think your outer join logic was wrong.
>>> Your ANSI join syntax example should be:
>>> SELECT ...
>>>   FROM mascustomer a RIGHT OUTER JOIN mascustaddr b
>>>        ON (a.customerid = b.customerid) AND (b.activeyn = 'Y')
>> Shouldn't this be a LEFT outer join?  From what I read in the thread
>> mascustomer is the master table from which all records should be pulled.
>>
>> SELECT ...
>>    FROM mascustomer a LEFT OUTER JOIN mascustaddr b
>>         ON a.customerid = b.customerid AND b.activeyn = 'Y'
>>
>> Cheers
>>
>>         robert

>
> I also think it should indeed be a left outer join, and it looks like
> the restriction is too tight now, as b.* is null in case no row is
> found for b, hence do not check for b.activeyn='Y', but
> nvl(b.activeyn, 'Y') = 'Y', assuming b.activeyn has a not null
> restriction. (If that's not the case a more elaborate check needs to
> be written.)
>
> SELECT ...
> FROM mascustomer a
> LEFT OUTER JOIN mascustaddr b
> ON a.customerid = b.customerid
> WHERE nvl(b.activeyn, 'Y') = 'Y'
>
> I also prefer, as you can see above, to separate the join condition
> and the where condition in the SQL. Join in the ON clause, filter in
> the WHERE clause. That is a real advantage re. readability of ANSI
> joins in Oracle 9i and on.

I agree with regard to the readability. However, your suggestion won't work. Your WHERE condition is too late. It will remove all columns from mascustaddr that have active = 'N' *after* the join and you will not get the proper result (i.e. too few rows). The condition must be in the join to remove active = 'N' columns before the join and leave proper NULL columns in place. Otherwise you will miss some rows from mascustomer in the result.

Kind regards

        robert Received on Mon Nov 26 2007 - 14:23:38 CST