SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help Understanding Query

    I have two tables

    blog
    • id
    • rtitle
    • ftitle
    • body
    • multi
    • category - linked to id in bcategory
    • tags
    • date

    bcategory
    • id - linked to category in blog
    • category


    I wrote a query to get the id, rtitle, body, multi, tags from the blog table and category from bcategory as follows:

    PHP Code:
    $query "SELECT blog.id, blog.rtitle, blog.body, blog.multi, blog.tags FROM blog LEFT JOIN bcategory ON blog.category = bcategory.id WHERE blog.ftitle = 'collapse-test' AND bcategory.category = 'site news'"
    Then I was looking at it and thought of a different way to do it

    PHP Code:
    $query "SELECT blog.id, blog.rtitle, blog.body, blog.multi, blog.tags FROM blog, bcategory WHERE blog.category = bcategory.id AND blog.ftitle = 'collapse-test' AND bcategory.category = 'site news'"
    They both give me the same results, so sorry for the long post, I'm just wondering which is better. I thought a JOIN was the only way, not that I really know how to write them, I was surprised when it worked. So when do I even need joins?
    Last edited by 700lbGorilla; Aug 9, 2007 at 23:01.

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the two queries are not doing the same, the second one is an INNER JOIN and thus is equivalent to
    Code:
    SELECT
      blog.id,
      blog.rtitle, 
      blog.body, 
      blog.multi, 
      blog.tags
    FROM blog INNER JOIN bcategory 
    ON blog.category = bcategory.id 
    WHERE blog.ftitle = 'collapse-test' 
      AND bcategory.category = 'site news';
    whereas in the first query you are using a LEFT OUTER JOIN.

    If you need to have an OUTER JOIN then you could only use the first query, if you need to have an INNER JOIN then the JOIN syntax is preferred over the syntax you have used in your second query (which, by the way, also is a JOIN even if it doesn't say so ;-) )
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Weird both queries give me the same results, I'll take a look at the link though. I've always been able to write them to get the results I need, just not sure when to use joins.

  4. #4
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not weird that both queries give you the same results, it just depends on the data in your tables. If you always have a value in blog.category and if you always have a corresponding entry for that value in your bcategory table, then a LEFT JOIN will give you the same results as an INNER JOIN.

    Regarding your last sentence ("just not sure when to use joins") I would assume that you still think that your second query isn't a JOIN. Although the keyword JOIN isn't in there, it is still a JOIN because you are joining blog and bcategory implicitly through your WHERE clause. You should really take a look at some of those SQL tutorials out there, maybe starting with the one I gave you above, in order to understand what INNER and OUTER JOINS are for and what's the difference between both.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

    Thumbs up

    kleineme, another excellent explanation

    you are a superb writer

    keep up the good work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    my mobile portal
    ghiris.ro

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks ernie

    that reminds me, i gotta get that picture updated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look at it this way

    table1 table2
    left right

    if you do a left join, you will get all matching records from the "left" table but only get info from the "right" table if they link to a record on the left.

    if you do an inner join you only get records if there are corresponding records in both tables.

    if you do a right join, you get all matching records from the right table, but only get corresponding records from the left table.

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That article helped explain it more, so if I don't have missing records since an article always belongs to a category, I would technically get the same results every time with any join since they are always linked with my table setup, correct?

  10. #10
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes. That is correct.

  11. #11
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I hope one day my table setups will have a missing field so I can actually prove to myself I understand when to use a corresponding join. Thanks for all the help everyone!

  12. #12
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    keep up the good work
    Thanks, Rudy, for your kind words! I will start by correcting myself:

    Quote Originally Posted by kleineme View Post
    Although the keyword JOIN isn't in there, it is still a JOIN because you are joining blog and bcategory implicitly through your WHERE clause.
    The statement would even be a JOIN if there were no WHERE clause at all, the two tables are joined implicitly just by putting them into the FROM clause ;-)
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


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
  •