Page 1 of 1
FROM a JOIN b vs. FROM a, b
Posted: Wed Jun 20, 2007 6:34 am
by Gente
Hi guys.
For a long time when I need to make a query on two or more tables I always use LEFT JOIN or INNER JOIN and never use list of tables separated by comma in FROM statement. Now it is some kind of principle for me.
In this forum I've already seen a lot of examples of using 'FROM a,b' construction.
Can you tell me maybe my principles is wrong?
Posted: Wed Jun 20, 2007 6:45 am
by superdezign
It does the exact same thing, except they have to define the ON conditions in the WHERE statement. Your way keeps it more organized, and I prefer it that way.
Posted: Wed Jun 20, 2007 10:11 am
by ReverendDexter
Agreed, they are doing basically the same thing, however, I prefer to use the "FROM a, b" format, so that I am specifying which fields to join on. Keep in mind, I'm almost always doing equijoins, so it makes more sense to me to see the join condition in the where clause.
I also cut my teeth on Oracle, so maybe that has something to do with it?
Posted: Wed Jun 20, 2007 3:12 pm
by califdon
Someone else may correct me on this, but I believe that putting all the tables in the FROM clause and the key equalities in the WHERE clause allows the MySQL engine more flexibility in determining an optimization plan for the query. For most of the work I do, the possible differences in optimization are unimportant, but for complex queries against huge tables, this might be something to consider. You might try using the EXPLAIN command to see if the two syntax options produce the same optimization.
Posted: Wed Jun 20, 2007 3:23 pm
by Benjamin
JOINS are harder on the database server in MySQL. I don't remember why offhand. I believe it has something to do with the number of records that need to be scanned or something similar. I'm sure someone can expand on this.
Posted: Wed Jun 20, 2007 4:26 pm
by Weirdan
JOINS are harder on the database server in MySQL.
Don't see how that would be the case. MySQL server still have to perform a join when you have more than one table in your WHERE clause. Explains show the same execution plan so, unless you could provide some testing results, I'm inclined to say you're most probably wrong.
Posted: Wed Jun 20, 2007 4:34 pm
by Benjamin
Weirdan wrote:JOINS are harder on the database server in MySQL.
Don't see how that would be the case. MySQL server still have to perform a join when you have more than one table in your WHERE clause. Explains show the same execution plan so, unless you could provide some testing results, I'm inclined to say you're most probably wrong.
When you hardcode a JOIN into a query the optimizer does not get to select the most efficient order to join them in. The tables are joined together in the order that you specified in the query, rather than leaving it to the optimizer to decide.
Posted: Wed Jun 20, 2007 4:42 pm
by Weirdan
The tables are joined together in the order that you specified in the query, rather than leaving it to the optimizer to decide.
Only if you specify STRAIGHT JOIN, which I'm sure you don't do often =)
Posted: Wed Jun 20, 2007 4:48 pm
by Benjamin
Weirdan wrote:
The tables are joined together in the order that you specified in the query, rather than leaving it to the optimizer to decide.
Only if you specify STRAIGHT JOIN, which I'm sure you don't do often =)
http://teaching-2.cs.uml.edu/MySQLdocs/ ... ation.html
Posted: Wed Jun 20, 2007 4:49 pm
by Weirdan
we're talking about inner joins here, you know....