FROM a JOIN b vs. FROM a, b
Moderator: General Moderators
- Gente
- Forum Contributor
- Posts: 252
- Joined: Wed Jun 13, 2007 9:43 am
- Location: Ukraine, Kharkov
- Contact:
FROM a JOIN b vs. FROM a, b
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?
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?
Last edited by Gente on Wed Jun 20, 2007 9:13 am, edited 1 time in total.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
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?
I also cut my teeth on Oracle, so maybe that has something to do with it?
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.
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.JOINS are harder on the database server in MySQL.
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.Weirdan wrote: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.JOINS are harder on the database server in MySQL.
http://teaching-2.cs.uml.edu/MySQLdocs/ ... ation.htmlWeirdan wrote:Only if you specify STRAIGHT JOIN, which I'm sure you don't do often =)The tables are joined together in the order that you specified in the query, rather than leaving it to the optimizer to decide.