Re: Conditional Join

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.

Regards,
Erik Received on Mon Nov 26 2007 - 11:16:42 CST