What's more efficient?

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

Moderator: General Moderators

User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

It's part of the INNER JOIN syntax: http://dev.mysql.com/doc/refman/5.0/en/join.html

The WHERE is part of the original SELECT
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

Kieran Huggins wrote:It's part of the INNER JOIN syntax: http://dev.mysql.com/doc/refman/5.0/en/join.html

The WHERE is part of the original SELECT
So basically "ON table1.id = table2.id" will only select records where the id from one table equals the id from another? I could do the same with the WHERE clause, but I guess it's more clear this way. Is there any other usages for the ON clause?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

JellyFish wrote:So basically "ON table1.id = table2.id" will only select records where the id from one table equals the id from another? I could do the same with the WHERE clause, but I guess it's more clear this way. Is there any other usages for the ON clause?
I believe that the ON selects records during the JOIN whereas WHERE would select records after the JOIN by scanning all the joined records. So ON is technically more efficient, but the way databases optimize queries these days that may or may not be actually true.
(#10850)
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

arborint wrote:
JellyFish wrote:So basically "ON table1.id = table2.id" will only select records where the id from one table equals the id from another? I could do the same with the WHERE clause, but I guess it's more clear this way. Is there any other usages for the ON clause?
I believe that the ON selects records during the JOIN whereas WHERE would select records after the JOIN by scanning all the joined records. So ON is technically more efficient, but the way databases optimize queries these days that may or may not be actually true.
That totally makes sense.

Also Kieran, you wrote a few days ago:

Code: Select all

SELECT * FROM users INNER JOIN tags_users ON users.id = tags_users.user_id WHERE (tags_users.tag_id = tag.id AND tag.name IN $tag_list
From this I see you can use fields from another table that's not being joined? That is you are writing "tag.id" and "tag.name" which is the tag table that's not specified in the INNER JOIN clause: "FROM users INNER JOIN tags_users ON users.id = tags_users.user_id". Is this possible in SQL; can you use fields from another table that's not being joined?
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

Can you use fields from another table that's not being joined?
Post Reply