On Jul 2, 12:02 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.
There is a difference between left and right. If there is ambiguity
between which table to "delete from" in a key-preserved joined view
between two tables, it will by default choose the left hand table
(meaning the first one listed in the table list).
Received on Mon Jul 02 2007 - 15:34:23 CDT
