SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2005
    Location
    New York, NY, U.S.
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Right joins question

    I understand that in the right join rows with values that don't correpond are dropped. such as follows:
    SELECT * FROM (authors RIGHT JOIN books ON authors.author_id = books.author_id)

    However, what if you were to join the tables with an inner join, but switched the tables. Such as:
    SELECT * FROM (books INNER JOIN authors ON books.author_id = authors.author_id)

    Wouldn't these joins have the same results? If so then why have a right join if you could just switch the tables around?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    to make this clearer, let's rewrite your queries a bit with the more common syntax:
    Code:
    SELECT *
      FROM authors
    RIGHT OUTER
      JOIN books
        ON authors.author_id = books.author_id
    Code:
    SELECT *
      FROM books
      JOIN authors
        ON authors.author_id = books.author_id
    with the right join, you will get a COMPLETE list of books, including ones that have no assigned author. in the case of no assigned author, then the fields normally displayed from the authors table would just be NULL.

    in the second query, you will only get books that have authors.

    also see this thread about left outer joins (similar to right joins): http://www.sitepoint.com/forums/show...04&postcount=2

  3. #3
    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)
    the question is, why is there a RIGHT OUTER JOIN when the LEFT OUTER JOIN works the same way?

    the following queries produce identical results:
    Code:
    SELECT *
      FROM authors
    RIGHT OUTER
      JOIN books
        ON authors.author_id = books.author_id
    Code:
    SELECT *
      FROM books
    LEFT OUTER
      JOIN authors
        ON authors.author_id = books.author_id
    my approach is always to use the LEFT OUTER JOIN
    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
  •