Joins Vs a simple where statement

[alexson]

I've been looking on these forums for a while and have noticed most people are using JOINs in their SQL rather than than just a plain WHERE statement such as:

SELECT t1.* FROM t1, t2 WHERE t1.primary_key = t2.foreign_key

I've always used this for simplicity as I'm rarely doing anything too complicated and as I'm self taught if something works I tend to stick with it.

Are there any reasons I should be using JOINs over, if they're best practice then why. I'm thinking more practical reasons such as speed/memory use/etc rather than more theoretical reasons.

Thanks

[r937]

best practice, because the WHERE clause is uncluttered when the join conditions have all been moved to the appropriate ON clause, hence easier to understand, hence less chance for accidentally forgetting a join condition

best practice, because the ON clauses now specifically highlight which conditions apply to each specific join (imagine a query which joins eleven tables, not just two, and having all those conditions in a huge mishmash in the WHERE clause)

best practice, because if you have to write an outer join, you have to use JOIN syntax anyway

best practice, because if you mix implicit comma joins with explicit JOIN joins, you get an error if you don't do it right

helps?

[alexson]

Thanks R937, I'll try and use them in the future

[flash-map]

best practice, because the WHERE clause is uncluttered when the join conditions have all been moved to the appropriate ON clause, hence easier to understand, hence less chance for accidentally forgetting a join condition

best practice, because the ON clauses now specifically highlight which conditions apply to each specific join (imagine a query which joins eleven tables, not just two, and having all those conditions in a huge mishmash in the WHERE clause)

best practice, because if you have to write an outer join, you have to use JOIN syntax anyway

best practice, because if you mix implicit comma joins with explicit JOIN joins, you get an error if you don't do it right

helps?

i have heard opposit. if you use join then it creates profermance issues with your query

[r937]

i have heard opposit. if you use join then it creates profermance issues with your query

that's just wrong

if you could please find out the link to the web site where you heard this misinformation, i will get it fixed