2

Hi I want to know which kind of queries are good with multiple tables.

For eg:

select table1.id, table1.name from table1,table2,table3 
where table1.id=table2.id and table2.id=table3.id

or 

select table1.id, table1.name  
from table1 
inner join table2 on table1.id=table2.id 
inner join table3 on table2.id=table3.id 
where 1

or 

select table1.id, table1.name 
from table1 
join table2 on table1.id=table2.id 
join table3 on table2.id=table3.id 
where 1

Which kind of query is best for performance?

learner
  • 2,609
  • 4
  • 22
  • 23

4 Answers4

5

They should be exactly the same. Probably you might want to read the corresponding section from the MySQL manual (which is only about syntax, not about performance, however).

michael667
  • 3,241
  • 24
  • 32
4

I would expect all 3 of these to be parsed as exactly equivalent, and will therefore perform identically. The where clauses in the latter two queries are redundant.

Many developers prefer the explicit, ANSI-92 syntax (as used in the latter 2 queries) over the implicit syntax in the first query - however, this should have no impact on performance.

1

As it has already been pointed out, all forms are the same regarding efficiency. They are different, however, in an also very important aspect: readability.

That's why I prefer to use the inner join syntax. It's also the notation prevalent in the standards.

You might find the answers to this question useful: SQL Inner Join syntax

Community
  • 1
  • 1
Xavi López
  • 27,550
  • 11
  • 97
  • 161
0

join is just a shortcut for inner join and doing from table1,table2 where is the same as an inner join too (see mysql documentation and mysql forum). they all should be treated the same way by mysql (the parsing-time for the second one might be a tiny bit lower, but thats negligible).

at least you should choose the one thats more radable (and maintainable that way) wich would be the second one (in my opinion, some guys might like the first one).

oezi
  • 51,017
  • 10
  • 98
  • 115
  • 1
    It's not the same any more if you mix comma and `JOIN`: the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on (from http://dev.mysql.com/doc/refman/5.0/en/join.html) – michael667 Oct 07 '11 at 11:02
  • 1
    -1, from table1, table2 is a **cross join** that you can limit the resultset in the where clause does not change that fact. MySQL also allows a cross join using inner join syntax, but that's just MySQL working against the SQL-standard, other DBMS's do not allow broken inner joins. – Johan Oct 07 '11 at 12:18