Which one is better way to join tables ?

[Amit Kumar M]

Which one is better way to join tables in SQL ?

SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.KEY = TABLE2.VALUE

OR

SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.KEY = TABLE2.VALUE

Although above queries produce the same result set but I am confused which one is more efficient and used widely.

thanks,
Amit

[rski]

For two tables as you put above there shouldn't be any difference.

For more tables using joins you can 'tell' the planner in what order tables should be joined.

[Rabbit]

I've heard that there is no difference but my experience has not seemed to back that up. Most of the time, using the inner join runs faster for me. So really, you just have to run it and see which one is faster.

As far as telling the planner in what order tables should be joined, you can't really do that. Most SQL engines will optimize the join order regardless of how you laid out the joins.

[rski]

As far as telling the planner in what order tables should be joined, you can't really do that. Most SQL engines will optimize the join order regardless of how you laid out the joins.

Rabbit I'm not sure if the Guys from postgres would agree with you

http://www.postgresql.org/docs/8.4/static/explicit-joins.html

[Rabbit]

We are talking about inner joins are we not? The article says that outer joins give the planner less freedom. But in this case, he is using inner joins, in which the planner gets to decide.

[rski]

Not only inner join.
Using join_collapse_l imit=1 you can make postgres to preserve join order given in query. And it works not only for OUTER JOIN I think.