Query Re-write

[rasprer]

Fellows...Any other way I can rewrite this outer join query.

select a.id
from app a, account b
where a.id = b.id(+)

 

[SantaMufasa]

Yes,

Here are some sample data to fit your scenario:

select * from app;

        ID
----------
         1
         3

select * from account;

        ID
----------
         1
         4

To obtain the same results as your code, you can specify any of these four syntax choices (in Oracle 9i):

select a.id
from app a, account b
where a.id = b.id(+);

        ID
----------
         1
         3

select a.id
from app a, account b
where b.id(+) = a.id;

        ID
----------
         1
         3

select a.id
from app a left outer join account b
on a.id = b.id;

        ID
----------
         1
         3

select a.id
from account b right outer join app a
on a.id = b.id;

        ID
----------
         1
         3

The neat, new feature of Oracle 9i is its support for "full outer joins":

select a.id a_id, b.id b_id
from account b full outer join app a
on a.id = b.id;

      A_ID       B_ID
---------- ----------
         1          1
                    4
         3

Let us know if this answers your question.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 01:12 (23Feb05) UTC (aka "GMT" and "Zulu"),
@ 18:12 (22Feb05) Mountain Time
Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[rasprer]

Mufasa....This is great. Thank you. On my original query, it seemed as if I was still doing a full table scan per the Explain Plan. It is my understanding that a full table scan will not utilize the appropriate indexes. Am I correct? This is the reason why I needed any other .sql suggestions that my eliminate a full scan.