Re: Outer Join to Multiple Tables

From: Ismo Tuononen <ismo_at_sasu1.carelian.fi>
Date: 1997/01/08
Message-ID: <ismo.852711531@sasu1>#1/1

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?

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(+);

>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.

>I suppose I could add an "empty" row with the value "'NULL'"
>for the key, set all the linking columns to "'NULL'", and remove
>the outer join: but that seems to me as being an awful lot of
>trouble, and doesn't at all match the intent of the query.

>Or am I making some wrong assumptions?

you are.

It's very nice that sybase and oracle have outer join (+) on different ends of equation, isn't it.

>Any suggestions would be massively appreciated.