FROM a JOIN b vs. FROM a, b

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
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

Post 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?
Last edited by Gente on Wed Jun 20, 2007 9:13 am, edited 1 time in total.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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 =)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

we're talking about inner joins here, you know....
Post Reply