Re: Conditional Join

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.