Re: Outer Join to Multiple Tables

Ismo Tuononen wrote:
>
> Alvin Sylvain <alvin_at_c-square.no.junk> writes:
>
> >I'm converting an application from Sybase to Oracle, and I've run
> >into a number of quandries.
 

> >In this case, Sybase allowed us to perform an outer join to several
> >tables besides the "main" one, and Oracle apparently will only do
> >this for one.
 

> >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
>
> Can you do this in sybase? and p.schoolname (+) = p.name?

The Sybase version would read:

   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 oracle you must write:
>
> select p.name, c.name, s.name
> from personnel p, company c, school s
> where p.companyname = c.name(+)
> and p.schoolname = p.name(+);

There is a distinct possibility that one or the other of us has got the meaning of "*=" and "(+) =" confused, and there's a strong possibility it's me!

I'm quite new at Oracle, and I'm quite confused!

Ie, what you've written above, if my understanding is correct, would be written in Sybase as follows:

   select p.name, c.name, s.name
   from personnel p, company c, school s    where p.companyname =* c.name
     and p.schoolname =* p.name

> >We could do this in Sybase. If the person was both unemployed
> >and not in school, you'd simply get his name and two nulls. If
> >he was in school, you'd get his name, a null, and the school's
> >name. Etc.
 

> >Oracle complains very explicitly that you can only perform an
> >outer join if the table is outer joined to exactly one other
> >table. This sucks.
>
> if you write:
> where p.companyname(+) = c.name
> and p.schoolname (+) = p.name;
> it means that you choose a line from personnel table either
> using company tables key or using school tables key or using
> company and school tables keys. and it's impossible to do.

Maybe the ticket is to change the "expression1 (+) = expression2" to "expression1 = expression2 (+)". Which means I'm going to have to do some serious reading in both manuals regarding the meaning and syntax of "outer join".

BTW, if anyone's interested, "standard" SQL specifies the outer join syntax with the keywords "left" or "right"!! Eg:

    where 1=1
      left and expression = expression

Go figure!      

+-------------------------+------------------------------------------+
| Alvin Sylvain           | If government is the answer, it's time   |
| alvin_at_c-square.com      | to re-evaluate the question.             |

+-------------------------+------------------------------------------+