SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Why left join?

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Los Angeles
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Why left join?

    Hello,

    I've been desiging a website in PHP & MySQL, and after writing a few dozen SQL queries, some relatively complex, I have yet to use a single LEFT JOIN. For example, let's say I want the country of a user. Users are stored in the users table, countries in the countries table. They both have primary keys of user_id and country_id, respectively. The users table has a foreign key of country_id, which is an int which references to a record in the countries table. Let's say I want the country of user with user_id 1. The syntax with left join is:

    Code:
    SELECT
        c.country
    FROM
        countries AS c
    LEFT JOIN
        users AS u
    ON  u.country_id = c.country_id
    WHERE
        user_id = 1
    Now if I were to forego use of left join, I'd have this:

    Code:
    SELECT
        c.country
    FROM
        countries AS c, users AS u
    WHERE
        c.user_id = u.user_id
    AND user_id = 1
    Is there anything wrong with the second one? I find it is easier to think that way instead of introducing new syntax. Perhaps there is some optimization that occurs on the first query? All responses very much appreciated. Thanks.

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    If I am not mistaken, the second query uses an INNER JOIN, not LEFT JOIN. The difference between those two is that if an INNER JOIN is used, there must be matching rows on both the left and right side (please correct me if I am wrong). If there is no matching row on the right side, the row on the left side is excluded from the result.

    Yours, Erik.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first query (LEFT JOIN) gives you all the records from the LEFT hand table (users) irrespective of wheter they have a matching entry in the RIGHT hand table (countries).

    The second query (implicit INNER JOIN) gives you record from the users and countris table only where the user has a country.

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Los Angeles
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So let's say I had a user with a country_id of 1000 (which no country_id of 1000 exists in the countries table). What difference would there be in the results? Considering that no c.country exists for a country_id of 1000.

    Also, implicit inner join, meaning there is no difference to MySQL if I used the second query or put the second query in inner join syntax?

    Thanks for the replies.

  5. #5
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your query there actually would be no difference if the country value in a user row was off the map because you have countries on the "LEFT" side.

    Flip the tables around to see some difference:
    Code:
    SELECT
        c.country
    FROM
        users AS u
    LEFT JOIN
        countries AS c
    ON  u.country_id = c.country_id
    WHERE
        user_id = USER-WITH-NON-EXISTING-COUNTRY
    And you are correct, there would be no difference between an INNER JOIN similar to your first example and your second example as it sits.

    Also, keep in mind that you can pretty much query and query and query to your heart's content without damaging anything... probably a really good way to figure out how things work.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here's an example of the difference

    suppose you wanted a list of all the countries, plus a count of all the users that have a user status of 7

    incorrect solution #1A (table list inner join) --
    Code:
    select c.country
         , count(*) as status7users
      from countries as c
         , users as u
     where c.country_id = u.country_id
       and u.status = 7
    incorrect solution #1B (JOIN syntax inner join) --
    Code:
    select c.country
         , count(*) as status7users
      from countries as c
    inner
      join users as u
        on c.country_id = u.country_id
     where u.status = 7
    incorrect solution #2 (LEFT OUTER JOIN syntax, WHERE condition) --
    Code:
    select c.country
         , count(*) as status7users
      from countries as c
    left outer
      join users as u
        on c.country_id = u.country_id
     where u.status = 7
    correct solution (LEFT OUTER JOIN syntax, ON condition) --
    Code:
    select c.country
         , count(*) as status7users
      from countries as c
    left outer
      join users as u
        on c.country_id = u.country_id
       and u.status = 7
    the reason #2 is wrong is because countries that have no users at all will not be included in the results
    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
  •