Results 1 to 7 of 7

Thread: SELECT query..

  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: SELECT query..

    Hi,

    I was wondering if the following can be improved using NATURAL JOIN:

    SELECT *
    FROM logins, profiles, avatars, prefs, groups, tmpl
    WHERE username='john'
    AND logins.some_id = profiles.some_id
    AND profiles.some_id=prefs.some_id
    AND profiles.avatar_id=avatars.avatar_id
    AND profiles.group_id=groups.group_id
    AND prefs.tmpl_id=tmpl.tmpl_id

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    17
    Assuming that 'some_id' exists in profiles and prefs, try this:
    Code:
    SELECT *
    FROM logins
    LEFT JOIN profiles USING (some_id)
    LEFT JOIN prefs USING (some_id)
    LEFT JOIN avatars ON profiles.avatar_id=avatars.avatar_id
    LEFT JOIN groups ON profiles.group_id=groups.group_id
    LEFT JOIN tmpl ON prefs.tmpl_id=tmpl.tmpl_id
    WHERE username='john'
    I'm not sure if this works too:
    Code:
    SELECT *
    FROM logins
    LEFT JOIN profiles USING (some_id)
    LEFT JOIN prefs USING (some_id)
    LEFT JOIN avatars USING (avatar_id)
    LEFT JOIN groups USING(group_id)
    LEFT JOIN tmpl USING(tmpl_id)
    WHERE username='john'

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Great thanks, roga!
    Code:
    SELECT *
    FROM logins
    LEFT JOIN profiles USING (some_id)
    LEFT JOIN prefs USING (some_id)
    LEFT JOIN avatars ON profiles.avatar_id=avatars.avatar_id
    LEFT JOIN groups ON profiles.group_id=groups.group_id
    LEFT JOIN tmpl ON prefs.tmpl_id=tmpl.tmpl_id
    WHERE username='john'
    The version about works but the other one doesn't.

    I'm curious if there're any performance differences between the one you gave above and the one I've originally using AND, reproduced below:
    Code:
    SELECT *
    FROM logins, profiles, avatars, prefs, groups, tmpl
    WHERE username='john'
    AND logins.some_id = profiles.some_id
    AND profiles.some_id=prefs.some_id
    AND profiles.avatar_id=avatars.avatar_id
    AND profiles.group_id=groups.group_id
    AND prefs.tmpl_id=tmpl.tmpl_id
    Thanks in anticipation

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    I'm curious if there're any performance differences between the one you gave above and the one I've originally using AND
    performance differences? probably

    but more significant is the difference in results

    your original query used inner joins, whereas roga's used left joins!!

    the results would be the same only if there happened to be no unmatched rows

    also, while the mysql manual is not very clear about this, a NATURAL join in standard sql does not have a USING clause, rather, it joins tables based on all identically named columns

    so the original query re-written with NATURAL joins would be

    SELECT *
    FROM logins
    NATURAL JOIN profiles
    NATURAL JOIN avatars
    NATURAL JOIN prefs
    NATURAL JOIN groups
    NATURAL JOIN tmpl
    WHERE username='john'

    if you use the convention of naming an auto_increment column as "id" and a relationship column as "some_id" then both tables would have an "id" column and the NATURAL join would try to join based on the that, which would of course be incorrect
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks, r937!
    the results would be the same only if there happened to be no unmatched rows
    So I should be using LEFT JOIN instead of AND?

    Can you give an example or perhaps elaborate on when LEFT JOIN should be used?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a LEFT join should be used whenever you want all rows from the left table, together with matching rows from the right table, whereas with an INNER join, you get only matching rows

    consider

    Pets
    1 dog
    2 cat
    3 bird
    4 ferret

    People
    35 curly
    38 larry
    39 moe

    PeoplePets
    35 2
    35 3
    39 1


    list all people, and their pets if any (LEFT join) --

    curly cat
    curly bird
    larry NULL
    moe dog

    list all people with pets (INNER join) --

    curly cat
    curly bird
    moe dog
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks for your example! I think I roughly get the idea

    cheers

Posting Permissions

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