1 Reply Latest reply: Feb 1, 2013 11:58 AM by 986451 RSS

    help of LEFT OUTER JOIN

    986451
      Hi Experts,

      Can you please help me with the below query on LEFT OUTER JOIN. I just want to know whether the below 2 queries are the same.

      1. select * from FROM hrm2_requests req LEFT OUTER JOIN hrm2_request_complaints hrm_comp LEFT OUTER JOIN hrm2_swap_complaint_detail comp_dtl LEFT OUTER JOIN hrm2_swap_complaint comp
      ON comp_dtl.complaint_code = comp.complaint_code
      ON hrm_comp.complaint_detail_code = comp_dtl.complaint_detail_code
      ON req.work_order = hrm_comp.work_order



      2. select * from hrm2_requests req LEFT OUTER JOIN hrm2_request_complaints hrm_comp
      ON req.work_order = hrm_comp.work_order
      LEFT OUTER JOIN hrm2_swap_complaint_detail comp_dtl
      ON hrm_comp.complaint_detail_code = comp_dtl.complaint_detail_code
      LEFT OUTER JOIN hrm2_swap_complaint comp


      i.e. SELECT * FROM TABLE1 LEFT OUT JOIN TABLE2 LEFT OUTER JOIN TABLE3 ON CONDITION <<>>
      is this equal to
      SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON CONDITION
      LEFT OUTER JOIN TABLE3 ON CONDITION

      because the cost of the 1st query is very high and i change the sql like 2nd, the cost reduced a lot.

      Thanks,
      Yuvaraaj.
        • 1. Re: help of LEFT OUTER JOIN
          William Robertson
          Each JOIN clause must contain an ON condition (except for CROSS JOIN but let's not go there) so your example isn't syntactically valid and I'm not sure what you are comparing to what. I would probably write it as:
          select *
          from   from hrm2_requests req
                 left join hrm2_request_complaints hrm_comp
                      on   hrm_comp.work_order = req.work_order
                 left join hrm2_swap_complaint_detail comp_dtl
                      on   comp_dtl.complaint_detail_code = hrm_comp.complaint_detail_code
                 left join hrm2_swap_complaint comp
                      on   comp.complaint_code = comp_dtl.complaint_code
          The order in which you arrange the join clauses will not affect performance.