right join equal to left join?

Question

For some time I have only been knowing how to use the INNER JOIN and absolutely no clue about what LEFT/RIGHT (OUTER) JOIN does. Although, as I just read about it, I cannot see what purpose the RIGHT has?

It seems to me it's identical to a reverse LEFT JOIN

If we follow my example:

SELECT t1.* FROM table1 t1 RIGHT JOIN table2 t2 ON t2.value = t1.value

Would be identical to:

SELECT t2.* FROM table2 t2 LEFT JOIN table1 t1 ON t1.value = t2.value

Is this right, or am I missing something out?

Yea I did some bad searching before posting, my bad! Clearly not only one duplicate... — Colandus, Feb 27 '13 at 19:54
It's possible that the ANSI committee on SQL created both RIGHT and LEFT OUTER JOIN so that SQL programmers could engage in endless advocacy debates about some language feature other than the validity of NULL! :-) — Bill Karwin, Feb 27 '13 at 19:58

score 3 · Answer 1 · answered Feb 27 '13 at 19:46

Yes, you're right. From Wikipedia:

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

Yes this is right, you can find more information if you searched:

Difference between left join and right join in SQL Server

edited May 23 '17 at 10:32

Community

1
1

answered Feb 27 '13 at 19:46

mlemay

1,622
2
32
53

score 1 · Answer 3 · answered Feb 27 '13 at 19:46

Right join is equivalent to a reverse left join.

That doesn't make it useless. Some people may prefer to write their queries that way. I am not one of those people. Personally, I almost never use right outer joins. And, no query should have both left and right outer joins.

There might be cases where someone would prefer:

select . . .
from table t right outer join
      (very
       long
       subquery
       here
      ) t2
      on . . .

To putting the table second.

I'm sorry for the upsetting title! :p – Colandus Feb 27 '13 at 19:52 — Colandus, Feb 27 '13 at 19:52

right join equal to left join?

3 Answers3

Linked