why right outer join is needed?

>> if in left join i reverse order of table it behaves like right join then why we need to separate joins? <<

Historical reasons. When we voted on this in ANSI X2H2, the proposal
included a whole list of in-fixed joins, most of them redundant and
not implemented. BUT they were easy to define once we had the
definition of an outer join. Here is the BNF

<joined table> ::=
<cross join> | <qualified join> | (<joined table>)

<cross join> ::= <table reference> CROSS JOIN <table reference>

<qualified join> ::=
<table reference> [NATURAL] [<join type>] JOIN
<table reference> [<join specification>]

<join specification> ::= <join condition> | <named columns join>

<join condition> ::= ON <search condition>

<named columns join> ::= USING (<join column list>)

<join type> ::= INNER | <outer join type> [OUTER] | UNION

<outer join type> ::= LEFT | RIGHT | FULL

<join column list> ::= <column name list>

<table reference> ::=
<table name> [[AS] <correlation name>[(<derived column
list>)]]
| <derived table>
[AS] <correlation name> [(<derived column list>)]
| <joined table>

<derived table> ::= <table subquery>

<column name list> ::=
<column name> [{ <comma> <column name> }...]
 >> Stay informed about: why right outer join is needed? 

Archana ( ) writes:
> i am having one query regarding joins
>
> if in left join i reverse order of table it behaves like right join
> then why we need to seperate joins?

Well, we don't. Personally, I never use RIGHT JOIN, and my head hurts
when I see code that does.


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: why right outer join is needed? 

>> Well, we don't. Personally, I never use RIGHT JOIN, and my head hurts when I see code that does. <<

LOL! Have you talked to an Arab or Chinese programmer? We Latin/Greek/
Cyrillic alphabet users have a very strong "left-to-right" mindset.

And, yes, I also freak out and re-arrange RIGHT OUTER JOINs in code I
have to maintain.
 >> Stay informed about: why right outer join is needed? 

It's not so much the left to right mindset as it is the top to bottom
mindset that makes the left join easier to read. If you format your
SQL so that each table is on it's own line in the FROM clause, then
the ones listed at the top are base tables and the ones on the bottom
are LEFT JOIN tables. That's why the LEFT JOIN is superior and easier
to read.

Consistency is more important, however. Bottoms up to anyone who
prefers RIGHT JOINS. )

-Eric Isaacs
 >> Stay informed about: why right outer join is needed? 

>> It's not so much the left to right mindset as it is the top to bottom mindset that makes the left join easier to read. <<

Ever seen Chinese books? Classically, the characters go from top to
bottom in a column, then the columns go from right to left. It is a
very easy format if you are reading a scroll that you turn with the
right hand.

Likewise, speech balloons in Chinese manga are on the sides of the
panels. Computer game programs put the user messages on the right side
of the screen in a vertical column, etc.

Composition paper has separate vertical columns of blue line boxes for
characters, with the columns numbered from right to left. Their
version of our elementary school horizontal blue line paper!

My observation from teaching SQL for a few decades now is that Hebrew,
Arabic and Chinese native readers will write more RIGHT OUTER JOINS
than use Latin/Greek Cyrillic native readers.

But then Engineers write on quadrille paper. What does that say about
them?
 >> Stay informed about: why right outer join is needed?