Re: Difference in Left Join, Right Join
- From: dean <deanbrown3d@xxxxxxxxx>
- Date: Mon, 02 Jul 2007 20:34:23 -0000
On Jul 2, 12:02 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On Jul 2, 8:49 am, Ankur <ank...@xxxxxxxxx> 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).
.
- References:
- Difference in Left Join, Right Join
- From: Ankur
- Re: Difference in Left Join, Right Join
- From: Charles Hooper
- Difference in Left Join, Right Join
- Prev by Date: Re: AutoCommit - How does it works?
- Next by Date: Re: xml conversion in pl/sql
- Previous by thread: Re: Difference in Left Join, Right Join
- Next by thread: Using FBI to enforce subsets of records to hold same values?
- Index(es):
Relevant Pages
|
