Re: outer join

On Dec 3, 2:05 am, "BigLearner" <Small.Lear..._at_gmail.com> wrote:
> Hai everyone,
>
> This is what I get from one of the sites when I searched for outer
> joins but my book says having + on right means outer join and if its on
> left means its left join but the example is the other way round.

The SQL standard does not define OUTER standing by itself. It is always LEFT OUTER, RIGHT OUTER, or FULL OUTER. In fact the word OUTER is optional in that form of the queries.

>
> does it make a difference when we swap the join condition? or the order
> the tables are listed in the from clause?

The LEFT and RIGHT terminology comes from the newer SQL standard syntax. Mapping that to Oracle's (+) syntax can be confusing as you found out.

>
> can someone explain to me how + works against right and left outer join
>
> my book and the articles that i see on the web are contradicting.
>
> dept.deptno (+) = emp.deptno
>
> records from emp would be returned even if no corresponding records
> exists in dept. is this left join or right join?

That's a right join, just as your example belows demonstrates. The order of the WHERE condition is all that matters, not the FROM, when using oracle's (+) syntax.

>
> SQL> select p.part_id, s.supplier_name
> 2 from part p right outer join supplier s
> 3 on p.supplier_id = s.supplier_id;
>
> PART SUPPLIER_NAME
> ---- --------------------
> P1 Supplier#1
> P2 Supplier#2
> Supplier#3
>
> SQL> select p.part_id, s.supplier_name
> 2 from part p, supplier s
> 3 where p.supplier_id (+) = s.supplier_id;
>
> PART SUPPLIER_NAME
> ---- --------------------
> P1 Supplier#1
> P2 Supplier#2
> Supplier#3
>
> SQL> select p.part_id, s.supplier_name
> 2 from part p, supplier s
> 3 where p.supplier_id = s.supplier_id (+);
>
> PART SUPPLIER_NAME
> ---- --------------------
> P1 Supplier#1
> P2 Supplier#2
> P3
> P4
>
> SQL> select p.part_id, s.supplier_name
> 2 from part p left outer join supplier s
> 3 on p.supplier_id = s.supplier_id;
>
> PART SUPPLIER_NAME
> ---- --------------------
> P1 Supplier#1
> P2 Supplier#2
> P4
> P3
>
> BigLearner

HTH,
   Ed Received on Tue Dec 05 2006 - 12:02:49 CST