Re: Outer Join

BigLearner wrote:

> Hai everyone,
>
> 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?
>
>
>
> 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.
>
> Does it make a difference when we swap the join condition? or the order
> the tables are listed in the from clause?
>
>
> 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
>

Also, you may want to start using ANSI standard syntax as the Oracle proprietary syntax is not very transportable to other database engines and may be deprecated (i.e. won't work) in future releases.

select a.col1, b.col2 from tab1 a left outer join tab2 b on a.col1=b.col1 where...

-- 
Michael Austin.
Database Consultant