Right joins question

[Mygly]

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?

[longneck]

to make this clearer, let's rewrite your queries a bit with the more common syntax:

SELECT *
  FROM authors
RIGHT OUTER
  JOIN books
    ON authors.author_id = books.author_id

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

[r937]

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:

SELECT *
  FROM authors
RIGHT OUTER
  JOIN books
    ON authors.author_id = books.author_id

SELECT *
  FROM books
LEFT OUTER
  JOIN authors
    ON authors.author_id = books.author_id

my approach is always to use the LEFT OUTER JOIN