Re: Using Joins

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 31 May 2009 10:40:39 +0200
Message-ID: <4A224287.2090206_at_gmail.com>



shweta.kaparwan_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 at
http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html?page=last http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/

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
Received on Sun May 31 2009 - 03:40:39 CDT

Original text of this message