Re: outer joins

Mike Burden wrote:

> I have a couple of questions regarding outer joins.
>
> 1) Is there some advantage to using (+) on only certain columns. The
> books suggest it must be applied to all columns when using multiple
> conditions. If this is the case then surely it's used in the wrong place
> and should be used somewhere in the from clause like MS Access. i.e T1
> OUTER JOIN T2. As the SQL checker does not complain if a (+) is missing
> is it possible to take advantge of the usage to get some ,not so
> obvious, functionality.
>
> 2) OK, why can't you use the OR or IN operator in an outer join. I'm
> sure there must be some logical reason in that using it won't do what
> you think ( or what I think anyway). I've used the decode to simulate
> the OR condition so if its so simple why can't SQL do it. The decode
> seems to do what I want so what am I Missing????

The outer join is an so important feature that you can't simply substitute it using
any simple AND / OR / NOT operators. Why ...let's see :

Table A:

FIRST SECOND

100            JOHN
101            PETER
102            MARY
103            SAM
104            FRED

Table B
FIRST       CLASS
--------------------
100            A
101            B
104            A


SELECT  a.FIRST, a.SECOND, b.CLASS
            from  table1 a, table2 b
            where a.first = b.first (+) ;

the result will be:

FIRST SECOND CLASS

100            JOHN                A
101            PETER              B
102            MARY
103            SAM
104            FRED                A

If you don't use outer join "(+)" symbol, the query becomes an ordinary inner join query and you will lose two rows.

If you insist not to use (+) symbol in your query and want to get the same result as above, you have to use a very complicate query to get your result ( which may involves UNION / MINUS or INTERSECT operators).

John K. Received on Mon Feb 08 1999 - 10:49:47 CST