Re: Difference in Left Join, Right Join

On Jul 2, 8:49 am, Ankur <ank..._at_gmail.com> wrote:
> Hi
>
> If we reverse the order of tables in a Left or Right Join we achieve
> the results of Right and Left Join.
>
> That is, a left join b is similar to b right join a.
>
> So, why do we have Left/Right Joins separately, instead of either one.
>
> Thanks

I believe that it is for ANSI SQL-92 compliance that both LEFT and RIGHT syntax exist.
  T1.C1 = T2.C1(+)
  T2.C1(+) = T1.C1

If you write SQL such that what you feel is the driving table is at the left of the equal sign (driving from left to right), the first method, the left outer join would be used. If you write SQL such that you place the driving table at the right of the equal sign, as in setting a column equal to a value, the second syntax, the right outer join makes sense. The above is Oracle outer join syntax, not ANSI 92 syntax, but it was used to illustrate a point. (Just because it is believed that Oracle should be driving from T1 to T2, does not mean that Oracle will not attempt to drive from T2 to T1 in such a situation.)

"Special Edition Using SQL" page 196:
"There are two operators used to build outer joins with the SQL-92 syntax, LEFT JOIN and RIGHT JOIN. The LEFT JOIN operator includes all records from the left of the operator; the RIGHT JOIN operator includes all records to the right of the operator."

"SQL for Smarties: Advanced SQL Programming" page 234: "The name LEFT OUTER JOIN comes from the fact that the preserved table is on the left side of the equality sign. Likewise a RIGHT OUTER JOIN would have the preserved table on the right-hand side, and the FULL OUTER JOIN would preserve both tables. These extended equality notations have a lot of problems, which is why they were not used in SQL-92." Outside of the world of databases, three lefts is the same as a right. Just like driving a vehicle, sometimes it makes more sense just to turn right when formatting a SQL statement. There may be a half dozen or even a dozen ways to construct a SQL statement to accomplish a task, Oracle just provides several methods so that the developer can write code that is both self documenting and efficient.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Jul 02 2007 - 11:02:25 CDT