SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast alexson's Avatar
    Join Date
    Jun 2010
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joins Vs a simple where statement

    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:

    Code SQL:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast alexson's Avatar
    Join Date
    Jun 2010
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks R937, I'll try and use them in the future

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    http://www.fla-shop.com - Interactive maps for websites
    Flash Maps for web developers

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by flash-map View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •