Re: Using Joins
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 1 Jun 2009 06:39:25 -0700 (PDT)
Message-ID: <da6d9c43-2bb2-43de-a60d-a6306a36cad5_at_u10g2000vbd.googlegroups.com>
On May 31, 5:55 am, shweta.kapar..._at_googlemail.com wrote:
> On May 31, 9:40 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > shweta.kapar..._at_googlemail.com schrieb:
>
> > > All,
>
> > > I have tables a and b as follows,
>
> > > SQL> select * from a
> > > 2 /
>
> > > X Y
> > > ---------- ------------------------------
> > > 1 A1
> > > 2 A2
> > > 3 A3
> > > 4 A4
> > > A99
>
> > > SQL> select * from b;
>
> > > X Y
> > > ---------- ------------------------------
> > > 3 B3
> > > 4 B4
> > > 5 B5
> > > 6 B6
>
> > > Query1:
>
> > > SQL> select a.x,count(b.x) from a , b where a.x = b.x(+) and a.x is
> > > not null group by a.x order by
> > > a.x;
>
> > > X COUNT(B.X)
> > > ---------- ----------
> > > 1 0
> > > 2 0
> > > 3 1
> > > 4 1
>
> > > Query2:
>
> > > SQL> select a.x,count(b.x) from a LEFT OUTER JOIN b ON a.x = b.x and
> > > a.x is not null group by a.x
> > > order by a.x;
>
> > > X COUNT(B.X)
> > > ---------- ----------
> > > 1 0
> > > 2 0
> > > 3 1
> > > 4 1
> > > 0
>
> > > Question :
> > > Since keyword (+) can be avoided if we use the ANSI equivalent
> > > keyword LEFT OUTER JOIN then why
> > > there is a difference in the output using using (+) ( query1) and
> > > using LEFT OUTER JOIN (query2) ?
>
> > > Regards
>
> > Have a look athttp://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition....
>
> > Best regards
>
> > Maxim
>
> > --
> > Why make things difficult, when it is possible to make them cryptic
> > and totally illogical, with just a little bit more effort?
>
> > Aksel Peter Jørgensen- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks Maxim.- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 1 Jun 2009 06:39:25 -0700 (PDT)
Message-ID: <da6d9c43-2bb2-43de-a60d-a6306a36cad5_at_u10g2000vbd.googlegroups.com>
On May 31, 5:55 am, shweta.kapar..._at_googlemail.com wrote:
> On May 31, 9:40 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > shweta.kapar..._at_googlemail.com schrieb:
>
> > > All,
>
> > > I have tables a and b as follows,
>
> > > SQL> select * from a
> > > 2 /
>
> > > X Y
> > > ---------- ------------------------------
> > > 1 A1
> > > 2 A2
> > > 3 A3
> > > 4 A4
> > > A99
>
> > > SQL> select * from b;
>
> > > X Y
> > > ---------- ------------------------------
> > > 3 B3
> > > 4 B4
> > > 5 B5
> > > 6 B6
>
> > > Query1:
>
> > > SQL> select a.x,count(b.x) from a , b where a.x = b.x(+) and a.x is
> > > not null group by a.x order by
> > > a.x;
>
> > > X COUNT(B.X)
> > > ---------- ----------
> > > 1 0
> > > 2 0
> > > 3 1
> > > 4 1
>
> > > Query2:
>
> > > SQL> select a.x,count(b.x) from a LEFT OUTER JOIN b ON a.x = b.x and
> > > a.x is not null group by a.x
> > > order by a.x;
>
> > > X COUNT(B.X)
> > > ---------- ----------
> > > 1 0
> > > 2 0
> > > 3 1
> > > 4 1
> > > 0
>
> > > Question :
> > > Since keyword (+) can be avoided if we use the ANSI equivalent
> > > keyword LEFT OUTER JOIN then why
> > > there is a difference in the output using using (+) ( query1) and
> > > using LEFT OUTER JOIN (query2) ?
>
> > > Regards
>
> > Have a look athttp://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition....
>
> > Best regards
>
> > Maxim
>
> > --
> > Why make things difficult, when it is possible to make them cryptic
> > and totally illogical, with just a little bit more effort?
>
> > Aksel Peter Jørgensen- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks Maxim.- Hide quoted text -
>
> - Show quoted text -
Yes, the second link is definitely a good to be reminded of.
- Mark D Powell --