Outer Join

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 Received on Sun Dec 03 2006 - 01:08:15 CST