Re: Specifying the type of join

Jason Byors wrote:
>
> How do you specify a left to right, and a right to left join in SQL.
> I can't seem to find it in any of the books I have looked in. Could
> you please e-mail me as well as posting to the newsgroup.
>
> Thank you,
> jb

I my understanding of a "left" or "right" join from "standard" SQL is correct, then what you are talking about is an "outer" join. What you do do is place a "(+)" on the "inner" side.

Eg, for a LEFT join, put the "(+)" on the right side: for a RIGHT join, put it on the left side. Again, this is assuming I understand what you're talking about.

I think I like the way it was explained in a couple of the "Learn Oracle by Your Next Job Interview" books commonly available. You put the "(+)" on the side where you expect some of the rows to be missing.

Eg: (LEFT JOIN)

    select p.name, c.name
    from person p, company c
    where 1=1
      and p.companyname = c.name (+)

Eg: (RIGHT JOIN)

    select p.name, c.name
    from person p, company c
    where 1=1
      and c.name (+) = p.companyname

The idea being, you have a personnel table and a company table. You want a list of all the people, and the company they work for. But, you're allowing for the fact that some of the people might be unemployed. In a standard join, those people would not show up at all. In the outer join, the people will show up, but the companyname would display as NULL.

Someone posted that you were talking about Performance Issues. If that's the case, feel free to ignore everything above!!

Alvin_at_c-square.com Received on Mon Jan 13 1997 - 00:00:00 CST