SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: LEFT vs RIGHT?

  1. #1
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)

    LEFT vs RIGHT?

    Is there a difference? A circumstance that dictates a LEFT join over a RIGHT join? Or can all LEFT's be rewritten as RIGHT's?

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    The difference is which table will always return values.

    Say you have the following data:

    Code:
    Table1 (rowId, fkid, rowvalue)
    1, 1, "Table1 Row1"
    2, 1, "Table1 Row2"
    3, 2, "Table1 Row3"
    4, 2, "Table1 Row4"
    5, 3, "Table1 Row5"
    6, 3, "Table1 Row6"
    7, 4, "Table1 Row7"
    8, 4, "Table1 Row8"
    9, 5, "Table1 Row9"
    10, 5, "Table1 Row10"
     
    Table2 (rowId, rowvalue)
    1, "Table2 Row1"
    2, "Table2 Row2"
    3, "Table2 Row3"
    4, "Table2 Row4"
    5, "Table2 Row5"
    6, "Table2 Row6"
    7, "Table2 Row7"
    8, "Table2 Row8"
    this query
    Code SQL:
    SELECT t1.rowValue AS t1Value
         , t2.rowValue AS t2Value
      FROM Table1 t1
      JOIN Table2 t2 ON t1.fkid = t2.rowId

    returns this:
    Code:
    t1Value         t2value
    "Table1 Row1"   "Table2 Row1"
    "Table1 Row2"   "Table2 Row1"
    "Table1 Row3"   "Table2 Row2"
    "Table1 Row4"   "Table2 Row2"
    "Table1 Row5"   "Table2 Row3"
    "Table1 Row6"   "Table2 Row3"
    "Table1 Row7"   "Table2 Row4"
    "Table1 Row8"   "Table2 Row4"
    and this
    Code SQL:
    SELECT t1.rowValue AS t1Value
         , t2.rowValue AS t2Value
      FROM Table1 t1
      LEFT JOIN Table2 t2 ON t1.fkid = t2.rowId

    will return this (notice the last two rows):
    Code:
    t1Value         t2value
    "Table1 Row1"   "Table2 Row1"
    "Table1 Row2"   "Table2 Row1"
    "Table1 Row3"   "Table2 Row2"
    "Table1 Row4"   "Table2 Row2"
    "Table1 Row5"   "Table2 Row3"
    "Table1 Row6"   "Table2 Row3"
    "Table1 Row7"   "Table2 Row4"
    "Table1 Row8"   "Table2 Row4"
    "Table1 Row9"   NULL
    "Table1 Row10"  NULL
    and finally this
    Code SQL:
    SELECT t1.rowValue AS t1Value
         , t2.rowValue AS t2Value
      FROM Table1 t1
     RIGHT JOIN Table2 t2 ON t1.fkid = t2.rowId

    will return (again, notice the rows at the end)

    Code:
    t1Value         t2value
    "Table1 Row1"   "Table2 Row1"
    "Table1 Row2"   "Table2 Row1"
    "Table1 Row3"   "Table2 Row2"
    "Table1 Row4"   "Table2 Row2"
    "Table1 Row5"   "Table2 Row3"
    "Table1 Row6"   "Table2 Row3"
    "Table1 Row7"   "Table2 Row4"
    "Table1 Row8"   "Table2 Row4"
    NULL            "Table2 Row5"
    NULL            "Table2 Row6"
    NULL            "Table2 Row7"
    NULL            "Table2 Row8"
    Hope that helps....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    I'm aware of the difference - but there is no difference between t1 LEFT JOIN t2 and t2 RIGHT JOIN t1.

    The question was more to the effect of 'Is there any circumstance which necessitates a LEFT instead of a right, or are they -always- completely interchangable just in reversing the table order'

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,788
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    4
    Code SQL:
    SELECT t1.rowValue AS t1Value
         , t2.rowValue AS t2Value
      FROM Table1 t1
      LEFT JOIN Table2 t2 ON t1.fkid = t2.rowId

    That left joinn should give the exact same result as the following right join.

    Code SQL:
    SELECT t1.rowValue AS t1Value
         , t2.rowValue AS t2Value
      FROM Table2 t2
      RIGHT JOIN Table1 t1 ON t1.fkid = t2.rowId
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what you should do that is important, is to pick one or the other and don't mix them because it is easy to mix up which table is being joined in which direction. This is especially true as your queries get more complex.

    my suggestion is to use LEFT or RIGHT depending on the direction you read text.

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    This link here (Coding Horror: A Visual Explanation of SQL Joins) may help as you can visualise it.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    All very good posts, though noone actually answered the question, i'm going to assume the implied answer is "No, there is no situation in which you must use one instead of the other".

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,788
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by StarLion View Post
    All very good posts, though noone actually answered the question, i'm going to assume the implied answer is "No, there is no situation in which you must use one instead of the other".
    Since

    TABLE1 LEFT JOIN TABLE2

    and

    TABLE2 RIGHT JOIN TABLE1

    are equivalent to one another and just involve switching which side of the join you specify which table there is no situation where one cannot be translated into the other. So if you want to only use left joins and never use right joins (or vice versa) you can.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


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
  •