Inner join vs Where

Question

Is there a difference in performance (in oracle) between

Select * from Table1 T1 
Inner Join Table2 T2 On T1.ID = T2.ID

And

Select * from Table1 T1, Table2 T2 
Where T1.ID = T2.ID

?

Answer 1

It is true that, functionally, both queries should be processed the same way. However, experience has shown that if you are selecting from views that use the new join syntax, it is important to structure your queries using it as well. Oracle's optimizer can get confused if a view uses a "join" statement, but a query accessing the view uses the traditional method of joining in the "where" clause.

Answer 2

The performance should be identical, but I would suggest using the join-version due to improved clarity when it comes to outer joins.

Also unintentional cartesian products can be avoided using the join-version.

A third effect is an easier to read SQL with a simpler WHERE-condition.

Answer 3

Functionally they are the same as has been said. I agree though that doing the join is better for describing exactly what you want to do. Plenty of times I've thought I knew how I wanted to query something until I started doing the joins and realized I wanted to do a different query than the original one in my head.

Answer 4

In a scenario where tables are in 3rd normal form, joins between tables shouldn't change. I.e. join CUSTOMERS and PAYMENTS should always remain the same.

However we must distinguish joins from filters. Joins are about relationships and filters are about partitioning a whole.

SQL-92 syntax encourages us to separate both concepts and it's preferred over old syntax that put both, joins and filters, on WHERE clause which is more cumbersome.

Answer 5

[For a bonus point...]

Using the JOIN syntax allows you to more easily comment out the join as its all included on one line. This can be useful if you are debugging a complex query

As everyone else says, they are functionally the same, however the JOIN is more clear of a statement of intent. It therefore may help the query optimiser either in current oracle versions in certain cases (I have no idea if it does), it may help the query optimiser in future versions of Oracle (no-one has any idea), or it may help if you change database supplier.

Answer 6

They're both inner joins that do the same thing, one simply uses the newer ANSI syntax.