By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

Which one is better way to join tables ?

P: 9
Which one is better way to join tables in SQL ?
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.KEY = TABLE2.VALUE
OR
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 13 '11 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 700
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.
Jan 14 '11 #2

Rabbit
Expert Mod 10K+
P: 12,365
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.
Jan 14 '11 #3

Expert 100+
P: 700
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/s...cit-joins.html
Jan 14 '11 #4

Rabbit
Expert Mod 10K+
P: 12,365
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.
Jan 14 '11 #5

Expert 100+
P: 700
Not only inner join.
Using join_collapse_limit=1 you can make postgres to preserve join order given in query. And it works not only for OUTER JOIN I think.
Jan 15 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.