mysql join or select where tablename1.col1 = tablename2.col1

Question

i have two tables implemented in mysql...

          id            name               password

           1            name1              password1
           2            name2              password2

AND

          id            age               address

           1            age1              address1
           2            age2              address2

Now i need the result of select query in the combined form

          id     name      password    age   address

          1      ....      ........     ...  .....  
          2      ....       .. ..  .    ..   ... ..

Now i tried it using two queries as follows...

SELECT * FROM table1, table2 WHERE table1.id=table2.id;

AND also i tried

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

I did got the result as i wanted... But I just wanna know which method is comparitively better taking into fact the no of the columns or data entries...???

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

By a previous StackOverflow question, it would seem that performance is often approximately equivalent. Try running EXPLAIN on each of these queries to see which runs better on your dataset—though it's quite possible that MySQL will follow the same process for each.

edited May 23 '17 at 11:56

Community

1
1

answered Jun 28 '12 at 20:42

Matchu

83,922
18
153
160

well...m..sorry to say...i've tried EXPLAIN as u said...but m afraid i m unaware of how to evaluate the current situation using it...coz m quite amateur in this scenario.. – Arjun K P Jun 28 '12 at 21:29
@akp: yeah, EXPLAINs can be hard to read at times, but learning what they mean is a very, very valuable skill to carry with you :) [Here's MySQL's rather dense reference doc](http://dev.mysql.com/doc/refman/5.0/en/explain-output.html), but if that ends up being overwhelming, I'm sure you can find a more straightforward explanation out on the web somewhere. – Matchu Jun 29 '12 at 01:11

score 0 · Answer 2 · answered Jun 28 '12 at 20:43

0

The most efficient way would be to have a single table. There is no reason that I see to break it up like that

answered Jun 28 '12 at 20:43

Kevin Brey

1,233
1
12
18

2

Could be it's just an abstraction to simplify the question for us. – Matchu Jun 28 '12 at 20:44
agreed..i've got situations where only one among the two tables is frequently used.....and this joining situations is used for few situations... – Arjun K P Jun 28 '12 at 21:27
you could make your commands: SELECT age, address FROM supertable; if you just need to access part of it. Normally you don't want to break tables up if thee is a one to one mapping. Meaning that since for each name and password there will only be one age and address and visa versa, you should use one table to store all of that data. However, as Matchu implied, this may not be the case and you are giving us a simplified view. In this case your question would be relevant. Say if you wanted to keep multiple addresses for each name, but in this case you would not put address with age; only id. – Kevin Brey Jun 30 '12 at 22:59

score 0 · Answer 3 · answered Jun 28 '12 at 20:43

0

What is "better"? Both methods are valid and should be the same in speed. But knowing that name, password, age and address are all properties of one person, it would be logical to store these values in one table...

answered Jun 28 '12 at 20:43

Sergey Eremin

10,994
2
38
44

agreed..i've got situations where only one among the two tables is frequently used.....and this joining situations is used for few situations... – Arjun K P Jun 28 '12 at 21:25

mysql join or select where tablename1.col1 = tablename2.col1

3 Answers3