In Oracle, in regards to syntax - how do I convert the (+) syntax to modern conventional JOIN?

Question

Suppose I have a FROM clause like so :

  FROM 

      apps.po_requisition_lines_all prl,  
      apps.po_requisition_headers_all prha,  
      po.po_req_distributions_all prda,  
      po.po_distributions_all pda,  
      po.po_headers_all pha 
  where 1=1  
      and prl.requisition_header_id= prha.requisition_header_id 
      and prl.requisition_line_id= prda.requisition_line_id 
      and prda.distribution_id= pda.req_distribution_id(+) 
      and pha.po_header_id(+)=pda.po_header_id 

Then how does this type of OUTER JOIN get converted if we want to use normal JOIN syntax ? thanks !

Answer 1

Without seeing the schema, I find it difficult but this should set you in the right direction:

 FROM apps.po_requisition_lines_all prl 
 INNER JOIN apps.po_requisition_headers_all prha ON prl.requisition_header_id = prha.requisition_header_id 
 INNER JOIN po.po_req_distributions_all     prda ON prda.requisition_line_id  = prl.requisition_line_id
 LEFT  JOIN po.po_distributions_all         pda  ON prda.distribution_id      = pda.req_distribution_id 
-- I note from the example provided that this is a right join
-- Without seeing the schema, it looks to me as though it should be left
-- As I say say, without seeing the schema, I probably shouldn't pass comment
 RIGHT JOIN po.po_headers_all               pha  ON pha.po_header_id          = pda.po_header_id;

For an INNER JOIN you can just say JOIN although I think that explicitly saying INNER aids readability. I also note the example provided has WHERE 1=1 which is redundant.

Answer 2

The + is old version of Outer Joins, and it differs where the + comes after equality sign or before it, But now it's recommended to use Join keywords instead of +, about the + sign if it comes:

After =:

select * from t1, t2
where t1.id=t2.id(+)

This means Left Outer Join:

select * from t1
left outer join t2 on t1.id=t2.id

---

Before =:

select * from t1, t2
where t1.id(+)=t2.id

This means Right Outer Join:

select * from t1
Right outer join t2 on t1.id=t2.id

---

Without +:

select * from t1, t2
where t1.id=t2.id

This means Inner Join:

select * from t1
join t2 on t1.id=t2.id