SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: where or join?

  1. #1
    SitePoint Zealot NZ Joe's Avatar
    Join Date
    Apr 2001
    Location
    Kapiti, New Zealand
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    where or join?

    Which is better?

    I have to select data from 5 different table in an already existing large database (so I can't change it, I just have to live with it).

    I'm not an SQL expert and I understand the construction of queries using where...and...and...etc better than join...join...join...where, but which is better?

    Should I just put up with sloppy looking but functional code?

    Or should I attempt to do joins?
    Gravity always wins

  2. #2
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Joins are a little confusing at first, but after awhile they become your best friend. Might as well get comfortable with them now and go ahead and use joins.

  3. #3
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As mongoloid stated, joins are a tad confusing at first. However, if you are selecting from 5 different tables (especially frequently) you will find that JOIN will become one of your new best friends.
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  4. #4
    SitePoint Zealot NZ Joe's Avatar
    Join Date
    Apr 2001
    Location
    Kapiti, New Zealand
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I'll study more
    Gravity always wins

  5. #5
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Here's a nice little example of using joins:

    http://www.w3schools.com/sql/sql_join.asp

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    JOIN syntax is confusing at first?

    first time i've heard that

    i think that by isolating each specific join condition away from all the other conditions would make it a lot less confusing

    JOIN syntax is definitely better

    for one thing, it's pretty hard to write an outer join with the WHERE syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    JOIN syntax is confusing at first?

    first time i've heard that
    We weren't all born with a SQL book in our hands like you Rudy!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    actually, i did not learn sql until i was in my late thirties

    but that was 17 years ago



    and there was no JOIN syntax at that time

    i learned the table list syntax first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist goughb's Avatar
    Join Date
    Sep 2000
    Location
    Chicago
    Posts
    526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The answer:

    Use joins, they are much faster then making multiple query calls from a while statement.

  10. #10
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think Rudy is on the mark with stating that a "many" table query is easier to read using "joins" than with "where/and" use. It helps in the way you format the query...especially when it comes to looking at it in the future.

    I would prefer:

    Code:
    select
      a.selcola,
      b.selcolb,
      c.selcolc
    from
      tableA a
    inner join
      tableB b
    on
      a.joincol = b.joincol
    inner join
      tableC c
    on
      a.joincol = c.joincol
    where
      b.somecond = 'blah'
    over:

    Code:
    select
      a.selcola,
      b.selcolb,
      c.selcolc
    from
      tableA a, tableB b, tableC c
    where
      a.joincol = b.joincol
      and a.joincol = c.joincol
      and b.somecond = 'blah'
    I think you will see that as you add more tables and true WHERE conditionals, the "where/and" method can get ugly fast.

    Also, when using "where/and" it is harder to discern the true WHERE conditionals (the "b.somecond = 'blah'" expression) from the necessary table-joining conditionals.

    Finally, if you ever need to alter the type of table join, it is typically a bit easier to do with the "join" example as opposed to then having to possibly re-write most of the "where/and" query to get an outer join in there.

    BTW, the "where/and" method is an implicit join.


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
  •