Re: Conditional Join

On Nov 30, 8:40 pm, ErikYkema <erik.yk..._at_gmail.com> wrote:
> On Nov 26, 9:23 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
>
>
> > 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
>
> Well, I wanted to see it for myself and I was right in my SQL in the
> above post, however you learned me that having a filter in the join
> clause acts sometimes differently from a filter in the where clause. I
> do not completely understand how/why. Regards, Erik Ykema
>
> -- Oracle XE on WinXP-SP4
>
> create table mast (
> customer integer
> )
> /
> create table address (
> customer integer
> , address varchar2(16)
> , activeyn char(1)
> )
> /
> insert into mast (customer) values (1)
> /
> insert into mast (customer) values (2)
> /
> insert into mast (customer) values (3)
> /
> insert into address (customer, address, activeyn) values (1, 'here',
> 'N')
> /
> insert into address (customer, address, activeyn) values (1, 'there',
> 'Y')
> /
> insert into address (customer, address, activeyn) values (2, 'moon',
> 'N')
> /
> commit
> /
>
> /* desired result as wished for by NitsR:
> customers 1, 3, since 1 has a valid address, 2 has addresses but no
> valids, and 3 has no addresses
> */
>
> select m.customer
> , ad.address
> from mast m
> left outer join address ad
> on m.customer = ad.customer
> where nvl(ad.activeYN, 'Y') = 'Y'
> /
> /* this gives the desired result: 1, 3
> */
>
> -- alternative SQL that in my believe was similar, however...
> select m.customer
> , ad.address
> from mast m
> left outer join address ad
> on m.customer = ad.customer
> and nvl(ad.activeYN, 'Y') = 'Y'
> /
> /* rows 1, 3, and also row 2!
> so the where clause is indeed different from the filters in the join
> clause - to my amazement!
> This is an odd thing to grasp for me...
> */
>
> -- old fashioned Oracle SQL
> select m.customer
> , ad.address
> from mast m
> , address ad
> where m.customer = ad.customer (+)
> and nvl(ad.activeYN, 'Y') = 'Y'
> -- 1 and 3 again.

Hi Robert,
I tried your statement too and see now the point you made: -- (gives 1, 2, 3): filter first, then join. select m.customer
, ad.address
, ad.activeYN
from mast m
left outer join address ad
  on m.customer = ad.customer
 and ad.activeYN = 'Y'

Thanks. I learned only now the difference between filtering before and after the join.
Regards, Erik Received on Fri Nov 30 2007 - 14:34:32 CST