Skip to main content
Question

LEFT OUTER joins syntax versus older version of outer joins. Any performance gains?


Forum|alt.badge.img+7

Hi All

 

I have a question regarding each of the following SQL statements. Can anyone tell me if there are any performance improvements/enhancements in ORACLE when using either Query 2 or Query 3 compared to the traditional way of writing outer join queries?

As an example, I have the following SQL queries which will identify orders which exist in the master/header table, but do not have lines.

Query 1 (Traditional way to write outer join queries)

SELECT * FROM  customer_order_tab  a
, customer_order_line_tab b
where a.order_no = b.order_no(+)
and   a.order_no = 'C10236'

 

Query 2 (using LEFT outer joins)

SELECT * FROM  customer_order_tab  a
LEFT OUTER JOIN  customer_order_line_tab b
ON a.order_no = b.order_no
WHERE   a.order_no = 'C10236'

Query 3 (using RIGHT outer joins)
SELECT * FROM  customer_order_line_tab   a
RIGHT OUTER JOIN  customer_order_tab  b
ON a.order_no = b.order_no
WHERE   b.order_no = 'C10236'
 

Thanks

2 replies

Forum|alt.badge.img+10
  • Hero (Customer)
  • 126 replies
  • March 20, 2024

That will depend upon which version of Oracle you are using.  The early implementations of the join syntax did not run as fast, but later implementations you will rarely see a difference in.


Forum|alt.badge.img+7
  • Author
  • Sidekick (Employee)
  • 48 replies
  • March 20, 2024

Thanks! 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings