1

I have recently learned that in Oracle we can do something like 

select t1.a, t2.b 
from table1 t1, table2 t2
where t1.x = t2.x

which is equivalent to 

select t1.a, t2.b
from table1 t1
join table2 t2 on (t1.x=t2.x)

Is there any performance difference or are there any other arguments that we should use one style instead of the other?

Warren
  • 991
  • 11
  • 28
  • 1
    I don't think there are any performance difference however the 2nd style is the latest (and recommended) one which provides much better readability. – beejm May 05 '17 at 10:06
  • 1
    The rdbms is clever enough to optimize your query to the second version. But you should do it yourself. – Tim Schmelter May 05 '17 at 10:08
  • @TimSchmelter Thanks, by saying that the RDBM will translate the query to the second type? Is it because it is closer the machine readable format? what happens in the middle of the process? – Warren May 05 '17 at 10:11
  • @TimSchmelter - hadn't realized my first draft comment was posted 8-) Anyway, check out the Jonathan Lewis piece I've linked to in my second one (on the accepted answer) – APC May 05 '17 at 10:30

1 Answers1

5

In general they are equal, however the lower one - also called "ANSI join syntax" should be preferred as recommended by Oracle and for many other reasons, see OUTER JOIN documentation.

There is only one exception (I am aware of): When you create a MATERIALIZED VIEW with FAST REFRESH then you have to use the old Oracle join syntax, otherwise FAST REFRESH is not possible.

Long time ago I created a SR at Oracle for that. Oracle does not consider this as a bug but rather a "lack of documentation". According to my knowledge this limitation/bug is still not resolved.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • 2
    When Oracle first introduced the ANSI 92 syntax in 9i they already had years of query optimization rules written for the old syntax. Rather than rewrite the optimizer under the covers they transform the ANSI 92 SQL to the older syntax and optimize that. This lead to occasional performance anomalies, especially (and not surprisingly) around outer joins. Over the years most these anomalies have disappeared but [there are still some around so be careful](https://jonathanlewis.wordpress.com/2010/12/03/ansi-argh/) – APC May 05 '17 at 10:30