473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Which one is better way to join tables ?

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
5 3780
rski
700 Expert 512MB
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
12,516 Expert Mod 8TB
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
rski
700 Expert 512MB
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
12,516 Expert Mod 8TB
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
rski
700 Expert 512MB
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

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

Similar topics

2
by: valexena | last post by:
1. Do you know in which tablespace the tables that store information about the Oracle online rollback segments is located? -- Posted via http://dbforums.com
0
by: Daniel Rossi | last post by:
Hi there i am trying to work out the most efficient way to list say multipl= e categories of entries, the database is quite large about 200 meg.=20 I would like to know if using join tables is...
4
by: Mark | last post by:
hi. i have two tables. table one ("users") contains two columns, "user_id", and "user_name" table two ("spam") contains three columns "msg_id","user_id", and "msg" basically, i want to...
2
by: zwasdl | last post by:
I'm using access to connect to Oracle via ODBC. I can also connect to Oracle via sql*plus. Can I write a query to join tables from different schema? If so, how? Thanks a million! Wei
2
by: =?Utf-8?B?VGVycnk=?= | last post by:
Is it possible to join tables from different databases? If so, what is the format of the select statement? Both databases are on the same server. TIA, -- Terry
1
by: yasinirshad | last post by:
Hi.. how to use joins for 4 tables.. in my query i have CALLS,STATUS,USERS,MESSAGES (4 tables). Query: "SELECT CALLS.CALL_ID, REQUESTOR, USERS.USER_NAME REQUESTOR_NAME, DESCRIPTION, TYPE, SCOPE,...
21
patjones
by: patjones | last post by:
Hi all: My newest project involves creating a small help desk database for our customer service division to use. What will happen is that an employee will call up, explain his/her issue, and...
5
Bob Ross
by: Bob Ross | last post by:
Is there a way to to create a single table from multiple tables in a dataset? I currently have a dataset with multiple tables and and relations connecting those tables. Table1 has many records to...
0
by: fantabk | last post by:
Hello, I have 2 tables that I need to join and update, the query look like bellow: UPDATE M1L INNER JOIN M1 ON (M1L.A1 = M1.A2) SET M1L.A3 = . WHERE xxx; In M1, we have multiple records...
1
by: liveevil | last post by:
Hi all, I'm trying to join 2 tables as follow Table 1: userId Name 1 Tom 2 John Table 2:
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.