left/right outer join

Hi,
can you help me to find the equivalent statement?
I have

select p.part_id, s.supplier_name
from part p, supplier s
where p.supplier_id = s.supplier_id (+);

the equivalent statement is

select p.part_id, s.supplier_name
from part p left outer join supplier s
on p.supplier_id = s.supplier_id;

or

select p.part_id,s.supplier_name
from supplier s right outer join part p
on s.supplier_id = p.supplier_id ;

If you want some help, explain exactly what you want the results to look like

Both the statements which you have provided are correct !

The trick to remember is :

If the table from which you require all data regardless of the join, is on the left then use LEFT OUTER JOIN, if it is on the right then use RIGHT OUTER JOIN.


In your query, PART is the table from which you require all data

1) In statement one PART table is on left(PART mentioned first after the FROM clause). So it LEFT OUTER JOIN

2) In statement two PART table is on the RIGHT(SUPPLIER mentioned first after the FROM clause ). So it should be RIGHT OUTER JOIN

Dont confuse the above example while using more than two tables. It is just an hint to remember.

Note : The compiler how ever flips the RIGHT or LEFT OUTER joins and reorganizes it to LEFT OUTER JOIN. It is only for users flexibility two joins are provided.

Hope this helps !

Cheers
Nandu
:)