Page 2 of 2

Posted: Sat Dec 29, 2007 5:55 pm
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

Posted: Tue Jan 01, 2008 11:52 pm
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?

Posted: Wed Jan 02, 2008 12:00 am
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.

Posted: Fri Jan 04, 2008 6:39 pm
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?

Posted: Mon Jan 07, 2008 7:53 pm
by JellyFish
Can you use fields from another table that's not being joined?