Re: Outer Join to Multiple Tables

Lun Wing San (Oracle) wrote:
>
> Alvin Sylvain wrote:
>
> > Eg: Say you have a personnel table, with a join to a company
> > table, but the person might not be employed, and a join to a
> > school table, but the person might not be in school, etc.
> >
> > select p.name, c.name, s.name
> > from personnel p, company c, school s
> > where 1=1
> > and p.companyname (+) = c.name
> > and p.schoolname (+) = p.name
>
> Yes, you can do it in Oracle. The outer join must be the
> outermost one is based on the execution plan of join.

As it turns out, I read the book completely wrong on this. What I was =supposed= to do was the following:

    select p.name, c.name, s.name
    from personnel p, company c, school s     where 1=1

      and p.companyname = c.name (+)
      and p.schoolname  = p.name (+)

In other words, the (+) goes on the side that might not return a matching row. For example, in this case, we want each and every person in the personnel table to show up, even if there is no matching company or school (unemployed or uneducated).

Ie, the proper translation from Sybase is as follows:

  Sybase                       Oracle
expression *= expression     expression = expression (+)
expression =* expression     expression (+) = expression

When I modified the query according to these rules, it worked fine!

> ---
> Name : Lun Wing San
> Title : Oracle Application Developer of Hong Kong Productivity Council
> Oracle Database Administrator and System Administrator of QRC
> Phone : (852)27885841

Thanks for the help!! :-)

alvin_at_c-square.com
NO JUNK E-MAIL, PLEASE!! Received on Fri Jan 10 1997 - 00:00:00 CST